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:
- 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:
- 18 months and under
- 19-48 months
- 49-83 months
- 84-131 months
- 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.
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