About
Shows active patients who have received one (and only one) dose of flu this season and who have received 0 or 1 doses in previous years.
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.
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