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
Caveats
Each data set requires substantial computation time in OP, so each SQL is listed separately as opposed to aggregated into a single report. The following design choices were made to further improve the run-time of the reports.
- The reports only include patients with a status of ACTIVE at the time the report is run. They may or may not have been active for the report dates you set.
- The reports only include patients who were >=1. For example, if a patient had no medications prescribed for the time period, they will not appear on the Total Medications report but, if the patient came in for three sick visits, during the report date set, the patient will show up on the Total Sick Visit report with Sicks=3.
SQL Code: Firebird
Total Sick Visits
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno, fname, lname, birthdat as dob, sicks from register inner join (select patno, count(cptcode) as sicks from archive_transactions where date1 between :Start_date and :end_date and archive_Flag = 1 and cptcode in ('99212','99213','99214','99215','99201','99202','99203','99204','99205') group by patno) a on a.patno = register.patno where status_pat = 'ACTIVE' order by sicks desc
Total High Level Visits
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno, fname, lname, birthdat as dob, highsicks from register inner join (select patno, count(cptcode) as highsicks from archive_transactions where date1 between :Start_date and :end_date and archive_Flag = 1 and cptcode in ('99214','99215','99204','99205') group by patno) a on a.patno = register.patno where status_pat = 'ACTIVE' order by highsicks desc
Total Time-Based Visits
To highlight and copy the code below to your clipboard, simply click the Copy button.
select patno, fname, lname, birthdat as dob, count(note_id) as time_visits from enc_note inner join register on register.patno = enc_note.patno where enc_note.date1 between :Start_date and :end_date and counseling_pc_of_visit_txt like 'Key factor%' and status_pat = 'ACTIVE' group by patno, fname, lname, birthdat order by time_visits desc
Total Medications
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno, fname, lname, birthdat as dob, rxs from register inner join (select patno, count(patno) as rxs from medications where date1 between :Start_date and :end_date group by patno) a on a.patno = register.patno where status_pat = 'ACTIVE' order by rxs desc
Total Referrals/Transitions of Care
To highlight and copy the code below to your clipboard, simply click the Copy button.
select patno, fname, lname, birthdat as dob, count(letterno) as number_of_referrals from referalletter rf inner join register on register.patno = rf.patno where rf.date1 between :Start_date and :end_date and status_pat = 'ACTIVE' and custom1 in ('REFERRAL','TRACKING ENTRY') group by patno, fname, lname, birthdat order by 5 desc
Total Phone Messages
To highlight and copy the code below to your clipboard, simply click the Copy button.
select e.pat_id as patno, fname, lname, birthdat as dob, count(msg_id) as distinct_msg_threads from esmg_org_msg e inner join register on register.patno = e.pat_id where parent_id is null and date1 between :Start_date and :end_date group by pat_id, fname, lname, dob order by 5 desc
Total Labs
To highlight and copy the code below to your clipboard, simply click the Copy button.
select patno, fname, lname, birthdat as dob, count(trnsxno) as number_of_labs_ordered from diag_test_order d inner join register on register.patno = d.patno where d.date1 between :Start_date and :end_date group by patno, fname, lname, dob order by 5 desc
SQL Code: MySQL
Total Sick Visits
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno, fname, lname, birthdat as dob, sicks from register inner join (select patno, count(cptcode) as sicks from archive_transactions where date1 between :Start_date and :end_date and archive_Flag = 1 and cptcode in ('99212','99213','99214','99215','99201','99202','99203','99204','99205') group by patno) a on a.patno = register.patno where status_pat = 'ACTIVE' order by sicks desc
Total High Level Visits
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno, fname, lname, birthdat as dob, highsicks from register inner join (select patno, count(cptcode) as highsicks from archive_transactions where date1 between :Start_date and :end_date and archive_Flag = 1 and cptcode in ('99214','99215','99204','99205') group by patno) a on a.patno = register.patno where status_pat = 'ACTIVE' order by highsicks desc
Total Time-Based Visits
To highlight and copy the code below to your clipboard, simply click the Copy button.
select REGISTER.patno, fname, lname, birthdat as dob, count(note_id) as time_visits from enc_note inner join register on register.patno = enc_note.patno where enc_note.date1 between :Start_date and :end_date and counseling_pc_of_visit_txt like 'Key factor%' and status_pat = 'ACTIVE' group by patno, fname, lname, birthdat order by time_visits desc
Total Medications
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno, fname, lname, birthdat as dob, rxs from register inner join (select patno, count(patno) as rxs from medications where date1 between :Start_date and :end_date group by patno) a on a.patno = register.patno where status_pat = 'ACTIVE' order by rxs desc
Total Referrals/Transitions of Care
To highlight and copy the code below to your clipboard, simply click the Copy button.
select REGISTER.patno, fname, lname, birthdat as dob, count(letterno) as number_of_referrals from referalletter rf inner join register on register.patno = rf.patno where rf.date1 between :Start_date and :end_date and status_pat = 'ACTIVE' and custom1 in ('REFERRAL','TRACKING ENTRY') group by patno, fname, lname, birthdat order by 5 desc
Total Phone Messages
To highlight and copy the code below to your clipboard, simply click the Copy button.
select e.pat_id as patno, fname, lname, birthdat as dob, count(msg_id) as distinct_msg_threads from esmg_org_msg e inner join register on register.patno = e.pat_id where parent_id is null and date1 between :Start_date and :end_date group by pat_id, fname, lname, dob order by 5 desc
Total Labs
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno, fname, lname, birthdat as dob, count(trnsxno) as number_of_labs_ordered from diag_test_order d inner join register on register.patno = d.patno where d.date1 between :Start_date and :end_date group by patno, fname, lname, dob order by 5 desc