We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (official version is 14.19.1), which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: HEDIS IMA

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.

Code

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
) myall