| 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 the number of active patients by Medical Home, VFC status, and age “bucket”. It can be used as an aid in allocating Vaccine Inventory among multi-location Practices. This report should not be used for a VFC annual enrollment report which requires you to give the number of vaccines/patients who have received vaccines at a specific location. For a VFC annual enrollment report, run SQL: VFC Status Count by Age Group.
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:
- 18 months and under
- 19-48 months
- 49-83 months
- 84-131 months
- 132 months and above
SQL Code: Firebird
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
SQL Code: MySQL
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(timestampdiff(month,birthdat,cast('today' 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
) t1 group by age_cat, VFC, loc_name
