| 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 requirements of the Merck HPV “Health in Practice” (HIP) reporting program and shows commercially-insured 13 year olds seen in the past 24 months along with their HPV vaccine completion status.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- This query is only for the Merck program. Please don't use it for any other purpose!
- It only looks at active patients.
- Patients must have had a sick or well visit note in the 24 months beyond the set reference date.
- It only looks at non-VFC patients (ie. commercially insured) who are 13 on the reference date of the query.
- For a 2 dose series to be considered complete, the child had to receive two doses of HPV9. This is different than ACIP guidelines.
SQL Code: Firebird
To highlight and copy the code below to your clipboard, simply click the Copy button.
select more.*,case when totalhpv = 3 then 'pass' when totalhpv = 2 and months_apart >=5 and age_at_first_shot < 15 and first_dose_was = 165 and last_dose_was = 165 then 'pass' else 'fail' end as metric_det
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,
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, v1.cvxcode as first_dose_was, v2.cvxcode as last_dose_was 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,
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 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' and (register.sickvno = '5') 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
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'))
) moreSQL Code: MySQL
To highlight and copy the code below to your clipboard, simply click the Copy button.
select more.*,case when totalhpv = 3 then 'pass' when totalhpv = 2 and months_apart >=5 and age_at_first_shot < 15 and first_dose_was = 165 and last_dose_was = 165 then 'pass' else 'fail' end as metric_det
from
(
select v.patno, cast(v.birthdat as date) as dob, v.age, cast(v.recent_visit as date) as recent_visit, cast(v.bday13 as date), cast(v.early_date as date), cast(v.last_date as date),
timestampdiff(year,birthdat,early_date) as age_at_first_shot,
timestampdiff(month,early_date,last_date) as months_apart,
v.totalhpv, v1.cvxcode as first_dose_was, v2.cvxcode as last_dose_was 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,
timestampdiff(year,birthdat,cast(:ref_date as date)) 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 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 ( '13') and register.status_pat = 'ACTIVE' and (register.sickvno = '5') and
date_add(recent_visit, interval 24 month) >= cast(:ref_date as date)
) 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
