This is primarily a discussion of the database view "viewDrugs" and how it manipulates the data in the Monitoring table in order to make the data easier to use for reporting. The script attached to this page adds supporting tables to OpenLDRDict and give examples of populating them.
Modifying the Data Structure
When looking at the monitoring table you will notice that the drug columns (ResistantDrugs, IntermediatDrugs, SensitiveDrugs) can each contain multiple drugs which are separated by a tilde (~) as the delimiter. The first thing I do in the view is to break these drugs out into an easier to access structure.
The following SQL snippet turns the original database row into rows containing each of the drugs listed in the ResistantDrugs column. The view will have three of these such snippets, one for each drug column.
SELECT m.RequestID, m.OBRSetID, m.Organism, 'Resistant' AS Susceptibility,
1 AS ResistantFlag, 0 AS IntermediateFlag, 0 AS SensitiveFlag,
Split.m.value('.', 'VARCHAR(100)') AS Drug
FROM (
SELECT RequestID, OBRSetID, Organism,
CAST ('<M>' + REPLACE( CASE WHEN RIGHT(ResistantDrugs,1) = '~' THEN LEFT(ResistantDrugs,LEN(ResistantDrugs)-1) ELSE ResistantDrugs END , '~', '</M><M>') + '</M>' AS XML) AS String
FROM Monitoring
WHERE ResistantDrugs Is Not Null AND ResistantDrugs <>''
) AS m CROSS APPLY String.nodes ('/M') AS Split( m )
This SQL basically changes the tilde delimited string to an XML string and then does the CROSS APPLY and SPLIT as a kind of CROSS TAB type function to put each elements into separate rows.
Notice the new column called Susceptibility. Because this SELECT is specific to ResistantDrugs I can hardcode the column value to say the specimen/organism/drug on this row is flagged as Resistant. When you do it for the SensitiveDrugs and IntermediateDrugs columns you will set the Susceptibility accordingly.
The next columns that have been added are three new columns to help count rows (Organism/Drug) which are Resistant, Intermediate or Sensitive. In this example you see I hard code the ResistantFlag for the row to 1 and the IntermediateFlag and SensitiveFlags to 0. The same ca be done when manipulating the drugs into rows for the ItermediateDrugs and SensitiveDrugs. When reporting it is then very easy to do a SUM on these columns for use in a GROUP BY or cross-tab.
When you UNION three of these together for Resistant, Intermediate and Sensitive you turn a row that looks like this:
Into this:
Reporting Helper Tables
There are two new tables that are not standard OpenLDRDict tables but they are tables put in place in order to help simplify the reports.
Organism_ReportingGroup
Organisms might be reported with slightly different naming conventions or spellings. There also may be multiple organisms that you want to report together. You can join Monitoring to this table on the Organism field and the ReportingGroup will be what you use in the report.
Drug_DrugClass
This allows you do combine some drugs together to simplify the reporting. Drugs often belong to a "class" of drugs and they do not need reported individually.
viewDrugs
The following view is pretty straight forward if you understand the sub-selects described above and which are UNION'ed together to produce individual rows for each request, organism and drug; along with the flags and indications as to susceptibility of the drug indicated on the row.
The yellow/green/blue highlighting covers the splitting of the different drugs into individual rows.
The outer SELECT simply JOINS Requests to Monitoring and then joins in the new individual rows with the drug information. Then it LEFT JOINS to get information about the ReportingGroup. Another LEFT JOIN gets information about the DrugClass. Any yet another LEFT join gets geographic information from the viewFacilities for the RequestingFacilityCode.
CREATE VIEW [dbo].[viewDrugs]
AS
-- SELECT * FROM viewDrugs ORDER BY Organism, ZoneName, Drug
-- before organism join 37,382
-- after organism join 36,327
SELECT allDrugs.Susceptibility, allDrugs.Drug, fac.Description AS FacilityDescription,
CASE WHEN fac.ProvinceName Is Null THEN 'Unknown Province' ELSE fac.ProvinceName END AS ProvinceName,
fac.DistrictName, ResistantFlag, IntermediateFlag, SensitiveFlag,
CASE WHEN g.OrganismReportingGroup Is Null THEN 'No Reporting Group' ELSE g.OrganismReportingGroup END AS OrganismReportingGroup,
CASE WHEN g.OrganismReportingCategory Is Null THEN 'No Reporting Category' ELSE g.OrganismReportingCategory END AS OrganismReportingCategory,
CASE WHEN d.DrugClass Is Null THEN 'No Drug Class' ELSE d.DrugClass END AS DrugClass,
mon.LOINCCode, mon.ORGANISM, mon.SurveillanceCode, mon.LIMSObservationCode, mon.LIMSObservationDesc, mon.LIMSOrganismGroup, mon.CodedValue, mon.ResultSemiquantitive, mon.ResultNotConfirmed, mon.ResistantDrugs, mon.IntermediateDrugs, mon.SensitiveDrugs, mon.MDRCode,
req.*
FROM Requests AS req
INNER JOIN Monitoring AS mon ON req.RequestID = mon.RequestID AND req.OBRSetID = mon.OBRSetID
LEFT JOIN OpenLDRDict.dbo.Organism_ReportingGroup AS g ON CAST(mon.Organism AS varchar(256)) COLLATE SQL_Latin1_General_CP1_CI_AS = g.Organism
LEFT JOIN
(
SELECT m.RequestID, m.OBRSetID, m.Organism, 'Resistant' AS Susceptibility, 1 AS ResistantFlag, 0 AS IntermediateFlag, 0 AS SensitiveFlag,
Split.m.value('.', 'VARCHAR(100)') AS Drug
FROM (
SELECT RequestID, OBRSetID, Organism,
CAST ('<M>' + REPLACE( CASE WHEN RIGHT(ResistantDrugs,1) = '~' THEN LEFT(ResistantDrugs,LEN(ResistantDrugs)-1) ELSE ResistantDrugs END , '~', '</M><M>') + '</M>' AS XML) AS String
FROM Monitoring
WHERE ResistantDrugs Is Not Null AND ResistantDrugs <>''
) AS m CROSS APPLY String.nodes ('/M') AS Split( m )
UNION
SELECT m.RequestID, m.OBRSetID, m.Organism, 'Intermediate' AS Susceptibility, 0 AS ResistantFlag, 1 AS IntermediateFlag, 0 AS SensitiveFlag,
Split.m.value('.', 'VARCHAR(100)') AS Drug
FROM (
SELECT RequestID, OBRSetID, Organism,
CAST ('<M>' + REPLACE( CASE WHEN RIGHT(IntermediateDrugs,1) = '~' THEN LEFT(IntermediateDrugs,LEN(IntermediateDrugs)-1) ELSE ResistantDrugs END ,'~', '</M><M>') + '</M>' AS XML) AS String
FROM Monitoring
WHERE IntermediateDrugs Is Not Null AND IntermediateDrugs <>''
) AS m CROSS APPLY String.nodes ('/M') AS Split( m )
UNION
SELECT m.RequestID, m.OBRSetID, m.Organism, 'Sensitive' AS Susceptibility, 0 AS ResistantFlag, 0 AS IntermediateFlag, 1 AS SensitiveFlag,
Split.m.value('.', 'VARCHAR(100)') AS Drug
FROM (
SELECT RequestID, OBRSetID, Organism,
CAST ('<M>' + REPLACE( CASE WHEN RIGHT(SensitiveDrugs,1) = '~' THEN LEFT(SensitiveDrugs,LEN(SensitiveDrugs)-1) ELSE ResistantDrugs END , '~', '</M><M>') + '</M>' AS XML) AS String
FROM Monitoring
WHERE SensitiveDrugs Is Not Null AND SensitiveDrugs <>''
) AS m CROSS APPLY String.nodes ('/M') AS Split( m )
) AS AllDrugs ON mon.RequestID = allDrugs.RequestID AND mon.OBRSetID = allDrugs.OBRSetID AND mon.Organism = allDrugs.Organism
LEFT JOIN OpenLDRDict.dbo.Drug_DrugClass AS d ON AllDrugs.Drug = d.Drug
LEFT JOIN OpenLDRDict.dbo.viewFacilities AS fac ON req.RequestingFacilityCode = fac.FacilityCode
WHERE mon.Organism Is Not Null AND mon.Organism <> ''
AND
(
(ResistantDrugs Is Not Null AND ResistantDrugs <>'') or (IntermediateDrugs Is Not Null AND IntermediateDrugs <>'') or (SensitiveDrugs Is Not Null AND SensitiveDrugs <>'')
)
Looking at the output you can now see how easy this data will be to do groupings on ProvinceName, DrugClass, and/or OrganismReportingGroup and to get Counts and sums of the ResistantFlag or SensitiveFlag columns. There are example Crystal Reports templates available for utilizing this data.