SQL: HPV Completion: VFC & Total HPV Vaccines Administered

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 shows all active patients that are 13-18 years old along with whether they have completed (“pass”) or not completed (“fail") the HPV series.

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

Caveats

  • Uses ACIP guidelines to determine whether a 2 or 3 dose-series is appropriate.
  • Prompts you for a reference date (to see current status, enters today’s date.   If you choose a date in the past, it will show currently-active patients who were 13-18 years old as of the reference date and whether they had completed the series as of that reference date.
  • Shows most recent visit (defined as date of most recent sick or well note in the chart).
  • The column VFC_NUMBER refers to the patient’s current VFC assignment. VFC assignment (1=Medicaid, 2=self pay, 5=commercial insurance/not eligible, etc) can be set in the patient’s chart in Demographics > Basic Information > VFC Eligibility.

SQL Code: Firebird

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

select staffname, patno, birthdat, vfc_number, age, recent_visit, totalhpv, metric_det from
(
select more.*,case when totalhpv = 3 then 'pass' when totalhpv = 2 and months_apart >=5 and age_at_first_shot < 15 then 'pass' else 'fail' end as metric_det

from
(
select v.staffname, v.patno, v.birthdat, v.sickvno as vfc_number, 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 staff1.staffname, register.patno, register.birthdat, register.sickvno, 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)

inner join staff1
on (staff1.staffid = register.addr_id)


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','14','15','16','17','18') 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
)


SQL Code: MySQL

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

select staffname, patno, cast(birthdat as date) as dob, vfc_number, age, cast(recent_visit as date) as recent_visit, totalhpv, metric_det from
(
select more.*,case when totalhpv = 3 then 'pass' when totalhpv = 2 and months_apart >=5 and age_at_first_shot < 15 then 'pass' else 'fail' end as metric_det

from
(
select v.staffname, v.patno, v.birthdat, v.sickvno as vfc_number, v.age, v.recent_visit, v.bday13, v.early_date, v.last_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 staff1.staffname, register.patno, register.birthdat, register.sickvno, 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)

inner join staff1
on (staff1.staffid = register.addr_id)


where (timestampdiff(year,birthdat,cast(:ref_date as date)))  in ( '13','14','15','16','17','18') 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
) t1