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 Count By Age Group and Medical Home Location

About

This report shows the number of active patients by medical home location, VFC status, and age “bucket”.  It can be used as an aid in allocating vaccine inventory among multi-site practices.

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

Caveats

  • Only active patients are included.
  • You are prompted for a reference date. The age provided is the age that the patient will be on the reference date (for current distribution, enter today’s date). 
  • Patients are attributed to a location based on their assigned medical home.
  • VFC eligibles have a VFC eligibility number of 1, 2, 3, 4.   “Not VFC” is any other code.
  • Age categories are:
  1. 18 months and under 
  2. 19-48 months
  3. 49-83 months
  4. 84-131 months
  5. 132 months and above
  • Do not use this for a VFC report, which requires you to give the number of vaccines/vaccinees at a particular site.  This report gives the number of active patients; not the number of children who received vaccines.

Code

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

select count(patno), age_cat, VFC, loc_name from
(
select a.*, case when age_in_months <=18 then 'Group 1:   0-18 mo' when age_in_months <=48 then 'Group 2: 19-47 months' when age_in_months <84 then 'Group 3:  4-6 yo' when age_in_months <132 then 'Group 4:  7-10 yo' else 'Group 5: 11+' end as age_cat
from
(

select patno, fname, lname, birthdat, loc_name, 
floor(datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast(:ref_date as date)))) as age_in_months,

case when sickvno <5 then 'VFC' else 'not VFC' end as vfc from register
inner join location on location.id = register.loc_id
 where status_pat = 'ACTIVE' and patno > 99 and lname <> 'TESTPATIENT'
) a
) group by age_cat, VFC, loc_name