| 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
Shows active patients bucketed by age groups and by PCP.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
None
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
with pts as (
select a.* , staffname,
case when age<1 then 'A' when age<6 then 'B' when age<11 then 'C' when age <15 then 'D' when age <22 then 'E' else 'F' end as sortable,
case when age<1 then '0<1' when age<6 then '1-5' when age<11 then '6-10' when age <15 then '11-14' when age <22 then '15-21' else '22+' end as bracket from (
select patno, addr_id, floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date)) )/12) as age from register
where patno >99 and status_pat = 'ACTIVE') a
inner join staff1 on staff1.staffid = a.addr_id
)
select n, bracket, staffname from (
select count(patno) as n, bracket, sortable, staffname from pts group by 2,3,4
order by sortable ) a
SQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
with pts as ( select a.* , staffname, case when age<1 then 'A' when age<6 then 'B' when age<11 then 'C' when age <15 then 'D' when age <22 then 'E' else 'F' end as sortable, case when age<1 then '0<1' when age<6 then '1-5' when age<11 then '6-10' when age <15 then '11-14' when age <22 then '15-21' else '22+' end as bracket from ( select patno, addr_id, timestampdiff(year,birthdat,curdate()) as age from register where patno >99 and status_pat = 'ACTIVE') a inner join staff1 on staff1.staffid = a.addr_id ) select n, bracket, staffname from ( select count(patno) as n, bracket, sortable, staffname from pts group by 2,3,4 order by sortable ) a
