| 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 is similar to HEDIS Immunization Metric for Adolescents (IMA). It takes all currently-active patients who were 13 years old as of a reference date you specify, and determines whether they have had 2 or 3 HPV, at least one dose of Td/Tdap, and at least one dose of meningitis ACWY vaccine. If all three of these metrics are ‘pass,’ then the child also gets a ‘pass’ for the entire IMA measure.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- Like in HEDIS, all vaccines must have been given by the 13th birthday to count.
- The report looks for patients who are CURRENTLY active (who may or may not have been active on the reference date).
- The patient’s most recent sick or well visit is shown in “recent visit” so you have the option of excluding patients who have registered for your practice but you have never seen.
- Like HEDIS, it does not permit exclusions for patient refusal of vaccines.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select myall.*, case when hpv_metric = 'pass' and tdap_metric = 'pass' and men_metric = 'pass' then 'pass' else 'fail' end as IMA_metric from
(
select patno, birthdat, age, early_date, last_date, bday13, age_at_first, totalhpv, tdaps,mens, recent_visit, case when totalhpv = 3 and last_date<= bday13 then 'pass' when totalhpv = 2 and months_apart >=5 and age_at_first_shot < 15 and last_date<= bday13 then 'pass' else 'fail' end as hpv_metric, case when tdaps >=1 then 'pass' else 'fail' end as tdap_metric, case when mens >=1 then 'pass' else 'fail' end as men_metric
from
(
select v.patno, v.birthdat, v.age, v.recent_visit, v.bday13, v.early_date, v.last_date,
(v.early_date-v.birthdat)/365.24 as age_at_first_shot,
floor(datediff(month, dateadd(day,-extract(day from v.birthdat)+1,v.birthdat), dateadd(day,-extract (day from v.birthdat)+1,v.early_date))/12) as age_at_first,
datediff(month, dateadd(day,-extract(day from v.early_date)+1,v.early_date), dateadd(day,-extract (day from v.early_date)+1,v.last_date)) as months_apart, v.totalhpv, tdaps, mens from
(
select register.patno, register.birthdat, recent_visit, dateadd(month,156,register.birthdat) as bday13, vtotal.totalhpv, first_HPV.early_date, last_hpv.last_date, tdaps, mens,
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
left outer join
(select min(vacdate) as early_date, patno from
(select cvxcode, patno, vacdate from vaccine1 where cvxcode in ('62','118','165') and vacdate<= :ref_date) vhraw
group by patno) first_hpv
on (first_hpv.patno = register.patno)
left outer join
(select max(vacdate) as last_date, patno from
(select vacname, patno, vacdate from vaccine1 where cvxcode in ('62','118','165') and vacdate<= :ref_date) vhraw
group by patno) last_hpv
on (last_hpv.patno = register.patno)
left outer join
(select count(cvxcode) as totalhpv, patno from vaccine1 where cvxcode in ('62','118','165') and vacdate<= :ref_date group by patno) vtotal
on (vtotal.patno = register.patno)
left outer join
(select count(cvxcode) as tdaps, patno from vaccine1 inner join register on register.patno = vaccine1.patno where cvxcode in ('09','113','115','138','139') and vacdate<= :ref_date and vacdate <=
dateadd(month,156,register.birthdat) and vacdate >= dateadd(month,120,register.birthdat) group by patno
) ttotal
on (ttotal.patno = register.patno)
left outer join
(select count(cvxcode) as mens, patno from vaccine1 inner join register on register.patno = vaccine1.patno where cvxcode in ('136','147','114','32','167')
and vacdate <=
dateadd(month,156,register.birthdat) and vacdate >= dateadd(month,132,register.birthdat)
and vacdate<= :ref_date group by patno) mtotal
on (mtotal.patno = register.patno)
left outer join
(select patno, max(date1) as recent_visit from
(select patno, date1 from enc_note union all select patno, date1 from physical )
group by patno) visits on (visits.patno = register.patno)
where floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast(:ref_date as date)))/12) in ( '13') and register.status_pat = 'ACTIVE'
) v
left outer join vaccine1 v1 on (v.patno = v1.patno) and (v.early_date = v1.vacdate) and (v1.cvxcode in ('62','118','165'))
left outer join vaccine1 v2 on (v.patno = v2.patno) and (v.last_date = v2.vacdate) and (v2.cvxcode in ('62','118','165'))
) more
order by patno
) myallSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select myall.*, case when hpv_metric = 'pass' and tdap_metric = 'pass' and men_metric = 'pass' then 'pass' else 'fail' end as IMA_metric from
(
select more.patno, birthdat, age, early_date, last_date, bday13, age_at_first, totalhpv, tdaps,mens, recent_visit, case when totalhpv = 3 and last_date<= bday13 then 'pass' when totalhpv = 2 and months_apart >=5 and
age_at_first_shot < 15 and last_date<= bday13 then 'pass' else 'fail' end as hpv_metric, case when tdaps >=1 then 'pass' else 'fail' end as tdap_metric, case when mens >=1 then 'pass' else 'fail' end as men_metric
from
(
select v.patno, v.birthdat, v.age, v.recent_visit, v.bday13, v.early_date, v.last_date,
floor(timestampdiff(year,birthdat,early_date)) as age_at_first_shot,
floor(timestampdiff(year,birthdat,early_date)) as age_at_first,
floor(timestampdiff(month,early_date,last_date)) as months_apart, v.totalhpv, tdaps, mens from
(
select register.patno, register.birthdat, recent_visit,
date_add(register.birthdat, interval 156 month) as bday13,
vtotal.totalhpv, first_HPV.early_date, last_hpv.last_date, tdaps, mens,
floor(timestampdiff(year,birthdat,cast(:ref_date as date))) as age from register
left outer join
(select min(vacdate) as early_date, vhraw.patno from
(select cvxcode, vaccine1.patno, vacdate from vaccine1 where cvxcode in ('62','118','165') and vacdate<= :ref_date) vhraw
group by patno) first_hpv
on (first_hpv.patno = register.patno)
left outer join
(select max(vacdate) as last_date, vhraw.patno from
(select vacname, patno, vacdate from vaccine1 where cvxcode in ('62','118','165') and vacdate<= :ref_date) vhraw
group by patno) last_hpv
on (last_hpv.patno = register.patno)
left outer join
(select count(cvxcode) as totalhpv, vaccine1.patno from vaccine1 where cvxcode in ('62','118','165') and vacdate<= :ref_date group by patno) vtotal
on (vtotal.patno = register.patno)
left outer join
(select count(cvxcode) as tdaps, vaccine1.patno from vaccine1 inner join register on register.patno = vaccine1.patno where cvxcode in ('09','113','115','138','139') and vacdate<= :ref_date and vacdate <= date_add(register.birthdat, interval 156 month) and vacdate >= date_add(register.birthdat, interval 120 month) group by patno
) ttotal
on (ttotal.patno = register.patno)
left outer join
(select count(cvxcode) as mens, vaccine1.patno from vaccine1 inner join register on register.patno = vaccine1.patno where cvxcode in ('136','147','114','32','167')
and vacdate <= date_add(register.birthdat, interval 156 month)and vacdate >= date_add(register.birthdat, interval 132 month)
and vacdate<= :ref_date group by patno) mtotal
on (mtotal.patno = register.patno)
left outer join
(select patno, max(date1) as recent_visit from
(select patno, date1 from enc_note union all select patno, date1 from physical ) t1
group by patno) visits on (visits.patno = register.patno)
where floor(timestampdiff(year,birthdat,cast(:ref_date as date))) in ( '13') and register.status_pat = 'ACTIVE'
) v
left outer join vaccine1 v1 on (v.patno = v1.patno) and (v.early_date = v1.vacdate) and (v1.cvxcode in ('62','118','165'))
left outer join vaccine1 v2 on (v.patno = v2.patno) and (v.last_date = v2.vacdate) and (v2.cvxcode in ('62','118','165'))
) more
order by patno
) myall
