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, where patients less than one year old are aged in months.

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 patno, date1, birthdat, age_on_dos from
(
select distinct a.*, case when year_age_on_dos = 0 then (round(mo_age_on_dos,0) || ' months') else ( round(year_age_on_dos,0) || ' yrs') end as age_on_dos 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 year_age_on_dos,
floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,date1))) as mo_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 year_age_on_dos,
floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,date1))) as mo_age_on_dos
from enc_note
inner join register on register.patno = enc_note.patno
where date1 between :start_date and :end_date
) a
)

SQL Code: MySQL

To highlight and copy the code below to your clipboard, simply click the Copy button.

select b.patno, b.date1, b.birthdat, b.age_on_dos from
(
select distinct a.*, case when year_age_on_dos = 0 then (round(mo_age_on_dos,0) || ' months') else ( round(year_age_on_dos,0) || ' yrs') end as age_on_dos from (
select physical.patno, physical.date1, register.birthdat,
floor(timestampdiff(year,birthdat,date1)) as year_age_on_dos,
floor(timestampdiff(month,birthdat,date1)) as mo_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, enc_note.date1, register.birthdat,
floor(timestampdiff(year,birthdat,date1)) as year_age_on_dos,
floor(timestampdiff(month,birthdat,date1)) as mo_age_on_dos
from enc_note
inner join register on register.patno = enc_note.patno
where date1 between :start_date and :end_date
) a
) b

About

Caveats

SQL Code: Firebird

SQL Code: MySQL

Related Articles