We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s 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: Merck Program HPV Completion Ages 19-26

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.

Code

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