Query for anesthetic medications by DEA schedule

[wjstarck]

For users of the EASy Anesthesia Module, here is a query that will list all of the patients who received anesthetic medications by DEA schedule for a given date range:

Code: Select all

SELECT DISTINCT anestheticrecord.AnestheticRecordNum,anestheticrecord.AnestheticDate, patient.PatNum as 'Patient#',patient.LName,patient.FName,
patient.Gender,patient.Birthdate,patient.Address,patient.Address2,patient.City,patient.Zip,
patient.HmPhone,patient.WirelessPhone,anesthmedsgiven.AnesthMedName,anesthmedsgiven.QtyGiven,
anesthmedsgiven.QtyWasted
FROM anestheticrecord
LEFT JOIN anesthmedsgiven ON anesthmedsgiven.AnestheticRecordNum = anestheticrecord.AnestheticRecordNum 
LEFT JOIN patient ON patient.PatNum = anestheticrecord.PatNum
WHERE DATE_FORMAT(anestheticdate,'%Y-%m-%d') >= '2011-01-01' AND 
DATE_FORMAT(anestheticdate,'%Y-%m-%d') <= '2011-12-31' AND
anesthmedsinventory.DEASchedule = 'II'
ORDER BY anestheticdate ASC

This example will list all the schedule II medications delivered to patients last year. You can change the schedule number and date ranges to suit your needs. If you used arabic numbers instead of roman numerals (or some other notation) when you set up the program, use those characters for DEASchedule.

[wjstarck]

Actually, this one is a bit more complete as it will list the DEASchedule as a table header on the far right, and clean the headers up so everything will fit on a printed page in landscape view:

Code: Select all

SELECT DISTINCT anestheticrecord.AnestheticDate as 'Date', patient.PatNum as 'Patient #',patient.LName as 'Last Name',patient.FName as 'First Name',
patient.Gender,DATE_FORMAT(patient.Birthdate,'%m/%d/%Y') as 'DOB',patient.Address,patient.Address2 as 'Addr2',patient.City,patient.State,patient.Zip,
anesthmedsgiven.AnesthMedName as 'Anesthetic Med',anesthmedsgiven.QtyGiven as 'Qty Given',
anesthmedsgiven.QtyWasted as 'Qty Wasted',anesthmedsinventory.DEASchedule as 'DEA Schedule'
FROM anestheticrecord
LEFT JOIN anesthmedsgiven ON anesthmedsgiven.AnestheticRecordNum = anestheticrecord.AnestheticRecordNum 
LEFT JOIN patient ON patient.PatNum = anestheticrecord.PatNum
LEFT JOIN anesthmedsinventory ON anesthmedsinventory.DEASchedule = (SELECT DEASchedule from anesthmedsinventory WHERE AnesthMedName = anesthmedsgiven.AnesthMedName)
WHERE DATE_FORMAT(anestheticdate,'%Y-%m-%d') >= '2011-01-01' AND 
DATE_FORMAT(anestheticdate,'%Y-%m-%d') <= '2011-12-31' AND anesthmedsinventory.DEASchedule = 'II'
ORDER BY anestheticdate ASC