About

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

Caveats

To see patients who will need a second dose, add a drop down filter to Age to limit it to patients under 9 years old.

Then, to see patients who are ready for their second dose, add a drop down filter to “Days Since” so that only >=28 days are shown. 

Code

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

Copy

select b.*, most_recent_flu, datediff(day, most_recent_flu,cast('today' as date)) as days_since_first_dose from

(

select a.* from (

select r.*, coalesce(total_prev,0) as prev, coalesce(curr_seas,0) as curr from

(

select register.patno,fname, lname, birthdat, 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 = 'ACTIVE' and patno >99 

) r

left outer join (select patno, coalesce(count(vacname),0) as total_prev from vaccine1 where vacdate <= '06/30/2018' and cvxcode in ('88','111','135','140','141','144','15','150','151','149','153','155','158','161','166','168','171') group by patno) p on p.patno = r.patno

left outer join (select patno, coalesce(count(vacname),0) as curr_seas from vaccine1 where vacdate >= '08/01/2018' and cvxcode in  ('88','111','135','140','141','144','15','150','151','149','153','155','158','161','166','168','171') group by patno) c on c.patno = r.patno

) a where curr=1 and prev<2

) b left outer join

(select patno, max(vacdate) as most_recent_flu from vaccine1 where cvxcode in ('88','111','135','140','141','144','15','150','151','149','153','155','158','161','166','168','171') group by patno) v on v.patno = b.patno