Important Content Update Message
We are currently updating the OP Help Center content for the release of OP 20. OP 20 (official version 20.0.x) is the certified, 2015 Edition, version of the Office Practicum software. This is displayed in your software (Help tab > About) and in the Help Center tab labeled Version 20.0. We appreciate your patience as we continue to update all of our content.

SQL: HPV Due Vaccines

About

This SQL is used to indicate who is due for HPV: active patients between 11-21 who haven't completed the series (either 2 or 3 doses) who are slightly past the minimum spacing requirements.

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

Caveats

This shows all active 11-21 year olds who:

  • Have had no HPV vaccines
  • Have had 1 dose of HPV:
  • >6 months ago (if this dose was given before the 15th birthday)
  • >2 months ago (if this dose was given after the 15th birthday)
  • Have had 2 doses of HPV if:
  • The first dose of HPV was after the 15th birthday
  • The second dose was >5 months ago

Code

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

select patno, birthdat, age, early_date as first_HPV_dose_date, last_date as second_HPV_dose_date, elapsed_months, age_at_first_shot, totalhpv as doses_received from
(
select v.patno, v.birthdat, v.age, v.bday13, v.early_date, v.last_date, datediff(month,v.last_date,cast('today' as date)) as elapsed_months,  (v.early_date-v.birthdat)/365.24 as age_at_first_shot,  datediff(month,v.early_date,v.last_date) as months_apart, v.totalhpv, v1.vacname as first_name, v2.vacname as last_name from
(
select register.patno, register.birthdat, dateadd(month,156,register.birthdat) as bday13, vtotal.totalhpv, first_HPV.early_date, last_hpv.last_date, floor(datediff(month,register.birthdat,cast('Now' as date))/12) as age from register
 left outer join 
(select min(vacdate) as early_date, patno from
    (select vacname, patno, vacdate from vaccine1 where vacname in ('HPV4','HPV9','HPV','HPV,NOS')) 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 vacname in ('HPV4','HPV9','HPV','HPV,NOS')) vhraw
group by patno) last_hpv
  on (last_hpv.patno = register.patno)

left outer join
(select count(vacname) as totalhpv, patno from vaccine1 where vacname in ('HPV4','HPV9','HPV','HPV,NOS') group by patno) vtotal
on (vtotal.patno = register.patno)

where floor(datediff(month,register.birthdat,cast('Now' as date))/12) between 11 and 21 and register.status_pat = 'ACTIVE'  and  (register.patno >= 99) and register.lname <> 'TESTPATIENT'
) v
left outer join vaccine1 v1 on (v.patno = v1.patno) and (v.early_date = v1.vacdate) and (v1.vacname in ('HPV4','HPV9','HPV','HPV,NOS'))
left outer join vaccine1 v2 on (v.patno = v2.patno) and (v.last_date = v2.vacdate) and (v2.vacname in ('HPV4','HPV9','HPV','HPV,NOS'))
) a
where ((a.totalhpv is null) or (a.totalhpv = 1) or (a.totalhpv = 2 and a.age_at_first_shot < 15 and a.months_apart<5))
and ((elapsed_months is null) or (elapsed_months >= 6 and age_at_first_shot <15) or (age_at_first_shot >15 and totalhpv = 1 and elapsed_months >= 2) or (age_at_first_shot>15 and totalhpv=2 and elapsed_months >=5))