This report shows all patients 18 and under who have received one or more immunizations of any type during a specified time period and classifies them into one of three age groups: <1 year old, 1-6 years old, and 7-18 years old. It also classifies them by VFC category number (1 = Medicaid, 2 = self pay, 3 = AIAN, 4 = underinsured, 5 = not VFC eligible). Many states require this in their annual VFC re-enrollments.
A sample image of this SQL report run in the Database Viewer is shown below:
- Patients are classified according to the last vaccine they received during the period. For example, a patient who received both a Medicaid (1) vaccine at 6 3/4 years old and an uninsured (2) vaccine at age 7 will only appear in this report as VFC = 2, age 7-18.
- Patients who were 19 or older when they received their most recent vaccine will not appear in the report.
- This includes all child vaccine recipients during the period; i.e it does not exclude those who have been inactivated.
- If you have created or use additional VFC categories (e.g. 6 = CHIP), they will also appear if at least one patient received a vaccine for that status during this period.
- Do not run the report for longer than a one-year period, as it will have unpredictable results around age binning.
To highlight and copy the code below to your clipboard, simply click the Copy button.
with vfcs as ( select distinct * from ( select b.*, case when age =0 then '<1' when age >0 and age <7 then '1-6' when age >6 and age<19 then '7-18' end as age_bin from ( select a.patno,birthdat, a.most_recent, b.vfc1, floor( datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,most_recent) )/12) as age from (select patno, max(vacdate) as most_recent from vaccine1 where vacdate between :start_date and :end_date and vfc1 <> 9 group by patno) a inner join (select patno, vacdate, vfc1 from vaccine1 where vacdate between :Start_date and :end_date and vfc1 <> 9) b on b.patno = a.patno and b.vacdate = a.most_recent inner join register on register.patno = a.patno ) b where age <19 ) ) select VFC1,under_1, one_to_6, seven_to_18 from ( select vfc1, count(patno) as under_1 from vfcs where age_bin = '<1' group by vfc1 ) a right outer join (select vfc1, count(patno) as one_to_6 from vfcs where age_bin = '1-6' group by vfc1) b on b.vfc1 = A.VFC1 right outer join (select vfc1, count(patno) as seven_to_18 from vfcs where age_bin = '7-18' group by vfc1) c on c.vfc1 = A.VFC1