How to generate DEA scheduled medication reports in EASy

Reports for delivery of scheduled medications are generated via the Reports > Queries module of Open Dental. For example, this query will list all of the medications delivered over a given date range for a particular DEA schedule. The example lists all schedule II medications delivered last year (2012 in this example). For other schedules, just change the roman numeral to the schedule you want. Just copy the text here, modify it to suit your needs, and paste it in the textbox in the Query window. 

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') >= '2012-01-01' AND 
DATE_FORMAT(anestheticdate,'%Y-%m-%d') <= '2011-12-31' AND anesthmedsinventory.DEASchedule = 'II'
ORDER BY anestheticdate ASC

If you entered your DEA schedule numbers differently (eg., '2' instead of 'II' for schedule II, then use that character instead). If you want it to print in reverse order, i.e., latest records first, just change the 'ASC' at the end to 'DESC'


Please sign in to leave a comment.
Powered by Zendesk