| 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 all active patients aged 14 - 17 months old along with the number of checkups they had prior to being 15 months old.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- This requires that 99381, 99382, 99391, or 99392 was billed within OP for the checkup to count.
- This uses the HEDIS definition of “15 months,” which is precisely 90 days after the first birthday (not always the same as 15 months).
- An infant who has transferred later into the practice (for example, if the infant transferred at 7 months old) will probably fail the metric, since the practice billed no checkups on that patient prior to the transfer (example, no checkups on the patient for the infant's first 6 months of life if the infant transferred at 7 months old). An insurance company, however, may have billed checkups from another practice.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.patno, a.birthdat, a.ins_carrier_code, a.age_in_months,a.staffname, case when checkup >= 6 then 'pass' else 'fail' end as w15_metric from
(
select r.patno, r.birthdat, r.ins_carrier_code, r.age_in_months, r.staffname, checkups.total_checkup as checkup from
(
select patno, birthdat, staffname, ins_carrier_code, datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date))) as age_in_months from register
inner join staff1 on staff1.staffid=register.addr_id
where status_pat = 'ACTIVE' and datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date))) >= 14 and datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date))) <=17
) r
left outer join (select patno, birthdat, count(cptcode) as total_checkup from archive_transactions inner join register on register.patno = archive_transactions.patno where cptcode in ('99381','99391','99382','99392') and archive_flag = 1
and date1 < dateadd(day,90,dateadd(year,1,birthdat))
group by patno, birthdat ) checkups on checkups.patno = r.patno
order by ins_carrier_code) aSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select a.patno, a.birthdat, a.ins_carrier_code, a.age_in_months,a.staffname, case when checkup >= 6 then 'pass' else 'fail' end as w15_metric from
(
select r.patno, r.birthdat, r.ins_carrier_code, r.age_in_months, r.staffname, checkups.total_checkup as checkup from
(
select register.patno, birthdat, staffname, ins_carrier_code,
floor(timestampdiff(month,birthdat,curdate())) as age_in_months from register
inner join staff1 on staff1.staffid=register.addr_id
where status_pat = 'ACTIVE' and
floor(timestampdiff(month,birthdat,curdate())) >= 14 and
floor(timestampdiff(month,birthdat,curdate())) <=17
) r
left outer join (select archive_transactions.patno, birthdat, count(cptcode) as total_checkup from archive_transactions inner join register on register.patno = archive_transactions.patno where
cptcode in ('99381','99391','99382','99392') and archive_flag = 1
and date1 < date_add(date_add(birthdat,interval 1 year),interval 90 day)
group by patno, birthdat ) checkups on checkups.patno = r.patno
order by ins_carrier_code) a
