About

A sample image of this SQL report run in the Database Viewer is shown below:

Caveats

• The report looks for patients who are CURRENTLY active (who may or may not have been active on the reference date).
• “Recent checkup” only shows if the checkup has been done in the previous 365 days from the reference date.  For example, a patient who had a checkup done 400 days before the reference date will show a blank. It will not the most recent reference date.
• Status date shows when patients’ status was last changed with your practice. This allows you to exclude patients who have, for example, only been active one month with your practice.
• By changing the age limits to read “Between 11 and 21,” you can make this into a AWC (Adolescent Well Checkup) HEDIS measure.

SQL Code: Firebird

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

Copy

select b.*, case when recent_checkup is not null then 'pass' else 'fail' end as metric from
(
select a.*, recent_checkup from 
(
select patno, birthdat, status_date, staffname, ins_carrier_code, floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast(:ref_date as date)))/12)  as age from register
inner join staff1 on staff1.staffid=register.addr_id
where status_pat = 'ACTIVE' 
) a 
left outer join
(select max(date1) as recent_checkup, patno from physical where date1 between :ref_date - 365 and :ref_date group by patno) p on p.patno = a.patno
where age between 3 and 6
) b

SQL Code: MySQL

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

Copy

select b.*, case when recent_checkup is not null then 'pass' else 'fail' end as metric from
(
select a.*, recent_checkup from 
(
select patno, birthdat, status_date, staffname, ins_carrier_code, floor(timestampdiff(month,birthdat,cast(:ref_date as date))) as age from register
inner join staff1 on staff1.staffid=register.addr_id
where status_pat = 'ACTIVE' 
) a 
left outer join
(select max(date1) as recent_checkup, patno from physical where date1 between :ref_date - 365 and :ref_date group by patno) p on p.patno = a.patno
where age between 3 and 6
) b