About

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

Caveats

• COVID vaccines must be entered into a patient's immunization record, including when not vaccinated by the Practice.
• The SQL presumes that boosters will be ACIP and CDC approved for ages 12+ and the minimum booster interval for mRNA vaccines will be 5 months.
• The report does not check that the vaccine was given at the correct interval.

Code

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

Copy

select pvb.patno, pvb.age, pvb.status_pat,
case when pvb.det <> 'P' then pvb.det
when total_covid_vax_recd =2 and first_cvx in (212,'212') then 'Janssen vaxed and boosted'
when total_covid_vax_recd =1 and first_cvx in (212,'212') and months_since_last <2 then 'Janssen vaxed, too soon for booster'
when total_covid_vax_recd = 1 and first_cvx in (212,'212') and months_since_last >=2 then 'Janssen vaxed, due for booster'
when total_covid_vax_recd = 2 and first_cvx in (207,'207',208,'208') and months_since_last >=5 then 'mRNA vaxed, due for booster'
when total_covid_vax_recd = 2 and first_cvx in (207,'207',208,'208') and months_since_last <5 then 'mRNA vaxed, too soon for booster'
when total_covid_vax_recd = 2 and months_since_last >=5 then 'nonspecific CVX records, probably due for booster'
when total_covid_vax_recd = 2 and months_since_last <5 then 'nonspecific CVX records, probably too soon for booster'
when total_covid_vax_recd = 1 and first_cvx in (208,'208',217,'217',218,'218' ) and days_since_last < 21 then 'Too soon for second primary Pfizer dose'
when total_covid_vax_recd = 1 and first_cvx in (208,'208',217,'217',218,'218' ) and days_since_last >= 21 then 'Due for second primary Pfizer dose'
when total_covid_vax_recd = 1 and first_cvx in (207,'207' ) and days_since_last < 28 then 'Too soon for second primary Moderna dose'
when total_covid_vax_recd = 1 and first_cvx in (207,'207' ) and days_since_last >= 28 then 'Due for second primary Moderna dose'
when total_covid_vax_recd = 3 and months_between_fl >=6 then '3 doses, likely boosted'
when total_covid_vax_recd = 3 and months_between_fl <6 then '3 doses, likely 3 dose primary series'
else 'P' end as det,

pvb.first_vax, pvb.last_vax ,pvb.total_covid_vax_recd, pvb.months_since_last, pvb.days_since_last, pvb.first_cvx, pvb.months_between_fl

from ( select ba.patno, ba.age, ba.status_pat, ba.first_vax ,ba.last_vax, ba.total_covid_vax_recd,
case when det = 'P' and booster_eligible = 'N' and total_covid_vax_recd = 2 then 'Completed primary series, not eligible for booster'
when det = 'P' and booster_eligible = 'N' and total_covid_vax_recd = 1 and days_since_last >=21 then 'Due for second dose of primary series'
when det = 'P' and booster_eligible = 'N' and total_covid_vax_recd = 1 and days_since_last <21 then 'Not yet due for second dose of primary series' when det <> 'P' then det else 'P' end as det , ba.months_since_last, ba.days_since_last, ba.first_cvx, ba.months_between_fl from
(
select br.patno, br.status_pat, br.age,
case when age<12 then 'N' else 'Y' end as booster_eligible, v1.total_covid_vax_recd,
case when total_covid_vax_recd is null then 'Needs primary dose(s)' else 'P'
end as det, first_vax, last_vax,
case when v1.total_covid_vax_recd >0 then
datediff(month, dateadd(day,-extract(day from last_vax)+1,last_vax), dateadd(day,-extract (day from last_vax)+1,cast('today' as date))) else 'X' end as months_since_last,
case when v1.total_covid_vax_recd >0 then
datediff(month, dateadd(day,-extract(day from first_vax)+1,first_vax), dateadd(day,-extract (day from first_vax)+1,last_vax)) else 'X' end as months_between_fl,
case when v1.total_covid_vax_recd >0 then
datediff(day,last_vax,cast('today' as date)) else 'X' end as days_since_last, v1.first_cvx
from (
select patno, status_pat, floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date)))/12) as age from register where status_pat in ('ACTIVE') and patno >99 and lname <> 'TESTPATIENT'
) br
left outer join (select a.*, first_cvx from
(select patno, min(vacdate) as first_vax, max(vacdate) as last_vax, count(vacname) as total_covid_Vax_recd from vaccine1 where vacname like 'COVID%' group by patno) a inner join (select patno, cvxcode as first_cvx, vacdate from vaccine1 where vacname like '%COVID%' ) v on v.patno = a.patno and v.vacdate=a.first_vax)
v1 on v1.patno = br.patno
where age >=5
) ba
) pvb
order by patno