SQL: VFC Status Count by Age Group

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 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:

Caveats

  • 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.

SQL Code: Firebird

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


SQL Code: MySQL

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

select a.VFC1,under_1, one_to_6, seven_to_18 from 
( 
select vfc1, count(patno) as under_1 from ( 
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(timestampdiff(year,birthdat,most_recent))  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 
) c )  vfcs where age_bin = '<1' group by vfc1 
) a 
right outer join 
(select vfc1, count(patno) as one_to_6 from ( 
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(timestampdiff(year,birthdat,most_recent))  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 
) c )  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 ( 
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(timestampdiff(year,birthdat,most_recent))  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 
) c )  vfcs where age_bin = '7-18' group by vfc1) c on c.vfc1 = A.VFC1