Notifications
Clear all
Topic starter
Good Afternoon Everyone!
I'm looking for anyone with a report or SQL Query (or info on tables to query) to show us the number of controlled substance prescriptions we have written.
Thanks!
Posted : July 13, 2017 6:01 am
To view schedule IIs
{MEDINFO.CLASSCODE} = "2"
Posted : July 13, 2017 7:24 am
Topic starter
That gives the medications in the database that are controlled, but not the patient's who have had an Rx... I'm looking for patients or a total count of Rxs written. Thanks
Posted : July 14, 2017 1:09 am
Topic starter
Your reply got me in the right direction, so thank you. Below is the SQL query used to get my data, FYI for anyone who ventures here in the future.
select PID, SDID, CLINICALDATE, ClassCode from PRESCRIB where ClassCode = '2' and CLINICALDATE > '2017-01-01 00:00:00.000' order by CLINICALDATE
Posted : July 14, 2017 1:18 am
Same,
Did you ever get a report figured out? I am also looking for this information but I also want the prescriptions written for the patients and by which provider.
Terry
Posted : July 31, 2017 5:13 am
You never posted a finished SQL query so I figured I would give it a shot for you here:
SELECT PERSON.SEARCHNAME, PERSON.PATIENTID, PRESCRIB.CLINICALDATE PRESCRIB_DATE, MEDICATE.DESCRIPTION DRUG_NAME,
PRESCRIB.CLASSCODE, ENTERED_BY.SEARCHNAME ENTERED_BY, AUTHORIZED_BY.SEARCHNAME AUTHORIZED_BY, SIGNED_BY.SEARCHNAME SIGNED_BY FROM PRESCRIB
INNER JOIN MEDICATE ON MEDICATE.MID = PRESCRIB.MID
INNER JOIN PERSON ON PERSON.PID = PRESCRIB.PID
INNER JOIN USRINFO ENTERED_BY ON ENTERED_BY.PVID = PRESCRIB.USRID
INNER JOIN USRINFO AUTHORIZED_BY ON AUTHORIZED_BY.PVID = PRESCRIB.PVID
INNER JOIN USRINFO SIGNED_BY ON SIGNED_BY.PVID = PRESCRIB.PUBUSER
WHERE PRESCRIB.CLASSCODE = 2 AND PRESCRIB.CLINICALDATE > '2017-01-01 00:00:00.000'
AND PRESCRIB.CHANGE = 2
ORDER BY PRESCRIB.CLINICALDATE
This should work on CPS, but it was tested on CEMR. The only thing I changed so that it works on CPS was the date format on the clinicaldate in the where clause. It gives you the patient name, patientid, and the EMR user who entered the prescription, authorized the prescription and signed it.
Posted : August 8, 2017 1:39 pm
