| 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 lists all patients seen between the two dates that you specify. It also lists their age on the date of service.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- "Seen” is defined as a sick note OR a well note on a given date of service.
- “Age” is defined as their age at the time of the visit, not necessarily their current age.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select distinct * from ( select patno, date1, birthdat, floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,date1))/12) as age_on_dos from physical inner join register on register.patno = physical.patno where date1 between :start_date and :end_date union select patno, date1, birthdat, floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,date1))/12) as age_on_dos from enc_note inner join register on register.patno = enc_note.patno where date1 between :start_date and :end_date )
SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select distinct * from ( select physical.patno, date1, birthdat, floor(timestampdiff(year,birthdat,date1)) as age_on_dos from physical inner join register on register.patno = physical.patno where date1 between :start_date and :end_date union select enc_note.patno, date1, birthdat, floor(timestampdiff(year,birthdat,date1)) as age_on_dos from enc_note inner join register on register.patno = enc_note.patno where date1 between :start_date and :end_date ) a
