| 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 patients sick and well notes written for a date range, grouped by date of service.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
Counts one note per patient, per report date range, even if there are multiple sick and well notes for the same patient on different days. The purpose is to get a count of how many unique patients had a note added to their medical record for a date range.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select date1 as DOS, Loc_id, loc1.loc_name, count(distinct(patno)) as Num_Pat_Notes from (select e.loc_id, e.patno, e.date1 from enc_note e where patno > 0 and e.date1 between :Begin_Date and :End_Date UNION select p.loc_id, p.patno, p.date1 from physical p where patno > 0 and p.date1 between :Begin_Date and :End_Date ) left join location loc1 on loc1.id = loc_id group by 1,2,3 order by 1,2
SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select date1 as DOS, Loc_id, loc1.loc_name, count(distinct(patno)) as Num_Pat_Notes from (select e.loc_id, e.patno, e.date1 from enc_note e where patno > 0 and e.date1 between :start_date and :end_date UNION select p.loc_id, p.patno, p.date1 from physical p where patno > 0 and p.date1 between :start_date and :end_date ) t1 left join location loc1 on loc1.id = loc_id group by 1,2,3 order by 1,2
