| There are two versions of this SQL: Firebird and MySQL. The MySQL version only applies to clients who are have migrated to MySQL.. All other Practices should continue to use the Firebird version of this code. Click to expand and copy the code you need. If you are unsure which code to use, please check with your Practice Administrator. |
About
This report shows the patient name, prescription information, prescriber, and pharmacy (for e-prescriptions) for prescriptions written between two dates you specify.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- It only includes prescriptions written by the practice (“Med - to dispense”) and not reference meds or freeform meds.
- Depending on how the practice has its pharmacy naming setup, the pharmacy name may appear in any of the last three columns of the report.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select patno,register.fname, register.lname, birthdat, date1 as rx_date, staffname as prescriber, cast(prescrib as char(2000)) as prescription_info, addressbook.fname as addressbook_first, addressbook.lname as addressbook_last, addressbook.company as addressbook_company from medications left outer join pharmacy on medications.pharmacy_id = pharmacy.addr_id inner join register on register.patno = medications.patno inner join addressbook on addressbook.addr_id = pharmacy.addr_id inner join staff1 on staff1.staffid = medications.p_addr_id where date1 between :Startdate and :enddate and purpose = 'Med - to dispense' order by patno, date1
SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select medications.patno,register.fname, register.lname, birthdat, date1 as rx_date, staffname as prescriber, prescrib as prescription_info, addressbook.fname as addressbook_first, addressbook.lname as addressbook_last, addressbook.company as addressbook_company from medications left outer join pharmacy on medications.pharmacy_id = pharmacy.addr_id inner join register on register.patno = medications.patno inner join addressbook on addressbook.addr_id = pharmacy.addr_id inner join staff1 on staff1.staffid = medications.p_addr_id where date1 between :start_date and :end_date and purpose = 'Med - to dispense' order by patno, date1
