We are currently updating the OP Help Center content for the release of OP 19, which is a member of the certified OP 14 family of products. OP 19’s official version is 14.19.1, which you may see in your software (such as in Help > About) and in the Help Center tabs labeled 14.19. You may also notice that the version number in content and videos may not match the version of your software, and some procedural content may not match the workflow in your software. We appreciate your patience and understanding as we make these enhancements.

SQL: VFC Status Count by Age Group

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.

Code

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