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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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