| 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 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
SQL Code: Firebird
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))SQL Code: MySQL
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,
floor(timestampdiff(month,v.last_date,curdate())) as elapsed_months,
floor(timestampdiff(year,birthdat,v.early_date))
as age_at_first_shot,
timestampdiff(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,
date_add(register.birthdat,interval 156 month) as bday13,
vtotal.totalhpv, first_HPV.early_date, last_hpv.last_date,
floor(timestampdiff(year,birthdat,curdate())) 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(timestampdiff(year,birthdat,curdate())) 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))
