This time of year always comes with a lot of requests for medication usage data from the Pharmacy Clinical Coordinator. This year has been no exception. The most recent request was for a report identifying all medications in the pharmacy drug master that were used less than ten times in 2008. Seemed simple enough. Right.Â
Pulling the data via a query was simple enough, but I didn’t want to have to present each case and sum the results on the actual report. Took me a couple of days, and a little help to find this little beauty…Sum(1). That’s it. Counted each unique drug instance over the period identified in the query. In this case all instances of a medication used less than 10 times in 2008.
The SQL is below. Note the location of the red text.
SELECT dbo_DrugFormulary.RX_NUMBER, dbo_DrugFormulary.GENERICNAME, Sum(1) AS totaluse, dbo_DrugFormulary.STR, dbo_DrugFormulary.UNIT, dbo_DrugFormulary.BRANDNAME, dbo_DrugFormulary.VOLUME, dbo_DrugFormulary.VOLUME_UNIT
FROM dbo_DRUGUSAGE_ARCHIVE RIGHT JOIN dbo_DrugFormulary ON dbo_DRUGUSAGE_ARCHIVE.DRUG_CODE = dbo_DrugFormulary.RX_NUMBER
WHERE (((dbo_DRUGUSAGE_ARCHIVE.START_DATE) Like “*2008*” Or (dbo_DRUGUSAGE_ARCHIVE.START_DATE) Is Null) AND ((dbo_DRUGUSAGE_ARCHIVE.PTNAME) Not Like “*zzz*”))
GROUP BY dbo_DrugFormulary.RX_NUMBER, dbo_DrugFormulary.GENERICNAME, dbo_DrugFormulary.STR, dbo_DrugFormulary.UNIT, dbo_DrugFormulary.BRANDNAME, dbo_DrugFormulary.VOLUME, dbo_DrugFormulary.VOLUME_UNIT
HAVING (((dbo_DrugFormulary.RX_NUMBER) Not In (“99999”)) AND ((Sum(1))<10))
ORDER BY dbo_DrugFormulary.GENERICNAME;
Resuts:
