SQL: Merck Program HPV Completion Ages 19-26

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 fulfills the Merck program requirement for “Number of active patients ages 19-26 seen in the past 24 months who have received three total doses of HPV (“pass”) or not (“fail”).

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

Caveats

This report is built according to the Merck specifications for the Health in Focus program and should not be used for other purposes such as recall.

SQL Code: Firebird

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

select metric_det, count(patno) as total_pts from
(
select more.*,case when totalhpv = 3 then 'pass' else 'fail' end as metric_det
from
(
select v.patno, v.birthdat, v.age, v.recent_visit, v.totalhpv from
(
select register.patno, register.birthdat, recent_visit, dateadd(month,156,register.birthdat) as bday13, vtotal.totalhpv, 
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 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 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 ( '19','20','21','22','23','24','25','26') and register.status_pat = 'ACTIVE'  and 
datediff(month, dateadd(day,-extract(day from recent_visit)+1,recent_visit), dateadd(day,-extract (day from recent_visit)+1,cast(:ref_date as date))) <=24
) v

) more

) group by metric_det


SQL Code: MySQL

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

select metric_det, count(patno) as total_pts from
(
select more.*,case when totalhpv = 3 then 'pass' else 'fail' end as metric_det
from
(
select v.patno, v.birthdat, v.age, v.recent_visit, v.totalhpv from
( select register.patno, register.birthdat, recent_visit, date_add(register.birthdat, interval 156 month) as bday13, 
vtotal.totalhpv, 
timestampdiff(year,birthdat,cast(:ref_date as date)) as age from register
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 patno, max(date1) as recent_visit from
      (select patno, date1 from enc_note union all select patno, date1 from physical   ) p

group by patno) visits on (visits.patno = register.patno)
where timestampdiff(year,birthdat,cast(:ref_date as date)) 
in ( '19','20','21','22','23','24','25','26') 
and register.status_pat = 'ACTIVE'  
and 
date_add(recent_visit, interval 24 month) >=  cast(:ref_date as date)
) v
) more
) t1 group by metric_det