Merged Sunquest - SQL Statement

SELECT MC_Order_View.PtNumber, MC_Order_View.PtName, MC_Order_View.AccNumber, MC_Order_View.CollDateODBC, MC_Order_View.FinalDateODBC, MC_Order_View.Specimen_Translation, MC_Order_View.BatCode, MC_Order_View.Organism_Translation, MC_Order_View.SuscepMethod, MC_Order_View.DrugCode, MC_Order_View.DrugName, MC_Order_View.DrugResult, MC_Order_View.ZoneSizeMIC, MC_Order_View.PtSex, MC_Order_View.BirthdateODBC, MC_Order_View.PtLoc, MC_Order_View.HospCode MC_Order_View.AccNumber, MC_Order_View.Birthdate, MC_Order_View.PtNumber, MC_Order_View.OrgCode, MC_Order_View.AdmitDateODBC, MC_Order_View.CollDateDJ, MC_Order_View.ResultInterp

FROM SYSTEM.MC_Order_View MC_Order_View

WHERE (MC_Order_View.FinalDateODBC>={d '2010-01-01'} AND MC_Order_View.FinalDateODBC<={d '2010-12-31'})

-------SAMPLES BELOW------

Baltimore-Washington Hospital

FROM SYSTEM.MC_Order_View MC_Order_View

WHERE (MC_Order_View.FinalDateODBC>={d '2010-01-01'} AND MC_Order_View.FinalDateODBC<={d '2010-12-31'})

Children’s National Medical Center

SELECT U_CHW_MC_ORDER_View.AccNumber, U_CHW_MC_ORDER_View.Birthdate, U_CHW_MC_ORDER_View.HospCode, U_CHW_MC_ORDER_View.PtLoc, U_CHW_MC_ORDER_View.PtName, U_CHW_MC_ORDER_View.PtNumber, U_CHW_MC_ORDER_View.FinalDateODBC, U_CHW_MC_ORDER_View.OrgCode, U_CHW_MC_ORDER_View.AdmitDateODBC, U_CHW_MC_ORDER_View.CollDateDJ, U_CHW_MC_ORDER_View.CollDateODBC, U_CHW_MC_ORDER_View.DrugCode, U_CHW_MC_ORDER_View.DrugResult, U_CHW_MC_ORDER_View.ResultInterp, U_CHW_MC_ORDER_View.Specimen_Translation

FROM SYSTEM.U_CHW_MC_ORDER_View U_CHW_MC_ORDER_View

WHERE U_CHW_MC_ORDER_View.OrgCode='SAURE' AND U_CHW_MC_ORDER_View.CollDateODBC>={d '2000-01-01'} AND (U_CHW_MC_ORDER_View.DrugCode='OXACIL' OR U_CHW_MC_ORDER_View.DrugCode='CLINDA')

ORDER BY U_CHW_MC_ORDER_View.PtLoc

Stanford University Medical Center

SELECT MC_Order_View.PtNumber, MC_Order_View.PtLoc, MC_Order_View.SuscepMethod, MC_Order_View.DrugCode, MC_Order_View.DrugResult, MC_Order_View.AccNumber, MC_Order_View.PtName, MC_Order_View.PtSex, MC_Order_View.BirthdateODBC, MC_Order_View.FinalDateODBC, MC_Order_View.CollDateODBC, MC_Order_View.Specimen_Translation, MC_Order_View.Organism_Translation, MC_Order_View.ZoneSizeMIC, MC_Order_View.DrugName, MC_Order_View.AdmitDateODBC

FROM SYSTEM.UUCI_MC_Order_View MC_Order_View

WHERE (MC_Order_View.CollDateODBC>={d '2008-03-01'} AND MC_Order_View.CollDateODBC<={d '2008-03-31'})

Sample Sunquest Queries for Microbiology test results

These queries are typically used within Crystal Reports (see second page). The date parameter reflects the time period. For NHSN reporting the period is usually monthly. For Tennessee State ELR reporting the period is daily.

Sample 1

FROM SYSTEM.U_CHW_MC_ORDER_View U_CHW_MC_ORDER_View

WHERE U_CHW_MC_ORDER_View.OrgCode='SAURE' AND U_CHW_MC_ORDER_View.CollDateODBC>={d '2000-01-01'} AND (U_CHW_MC_ORDER_View.DrugCode='OXACIL' OR U_CHW_MC_ORDER_View.DrugCode='CLINDA')

ORDER BY U_CHW_MC_ORDER_View.PtLoc

Sample2

FROM SYSTEM.UUCI_MC_Order_View MC_Order_View

WHERE (MC_Order_View.CollDateODBC>={d '2008-03-01'} AND MC_Order_View.CollDateODBC<={d '2008-03-31'})