We are currently updating the OP Help Center content for the release of OP 14.19 or OP 19. OP 19 is a member of the certified OP 14 family of products (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: 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))