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