About

If your practice participates in the Reach Out and Read program, this report tells you how many checkups you did between dates you specify that fell in one of several age "bins" between 6 months and 5 years old.

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

Caveats

1. The dates you enter at the prompt are the dates between which the checkups were done. The ages returned are thus the ages of patients when the checkups were done, which is not necessarily the same age these patients are now
2. You must be using OP’s billing for this SQL to work, and you must have billed 99381-99383 or 99391-99393 for it to count as a checkup.
3. This report includes all patients who had checkups during the period – both active status and inactive statuses.
4. This report counts distinct checkups, not distinct patients. A baby who has a checkup at 6 months old and at 9 months old will appear in both those bins if a report is run that includes both checkup dates.
5. Age “binning” is a close estimation, but not perfect. For example, children who are 180 days old are presumed to be 6 months old here. While this estimation is not enough for work that requires precision dating, e.g. vaccine computations, it runs more quickly, and it is adequate for the purposes of Reach Out and Read.
6. Age binning rounds down. For example, let’s say a child has a checkup at 8 months old. Theoretically, they could be counted as a 6 month checkup or a 9 month checkup; Reach out and Read does not recognize an 8 month old checkup category. This report classifies them as a 6 month checkup, i.e. the lower of the two possible bins. Similarly, 9, 10, or 11 month old checkups all go in the 9 mo bin.

SQL Code: Firebird

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

Copy

select count(cptcode) as number_of_checkups, classif, loc_name from
(
select a.*, case when elapsed < 180 then '<6 mo' when elapsed >2191 then 'too old' when elapsed between 180 and 270 then '6 mo' when elapsed between 271 and 365 then '9 mo' when elapsed between 366 and 456 then '12 mo' when elapsed between 457 and 548 then '15 mo' when elapsed between 549 and 729 then '18 mo' when elapsed between 730 and 913 then '24 mo' when elapsed between 914 and 1095 then '30 mo' when elapsed between 1096 and 1461 then '3 yo' when elapsed between 1462 and 1826 then '4 yo' when elapsed between 1827 and 2191 then '5 yo' else   'not classed' end as classif
from
(
select cptcode, date1, birthdat, date1-birthdat as elapsed , loc_name from archive_transactions
inner join register on register.patno = archive_transactions.patno
left outer join location on location.id = archive_transactions.loc_id
where date1 between :start_date and :end_date and cptcode in ('99391','99392','99393','99381','99382','99383')
and archive_flag  <=1
) a
) group by classif, loc_name order by 3,2

SQL Code: MySQL

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

Copy

select count(cptcode) as number_of_checkups, classif from
(
select a.*, case when elapsed < 180 then '<6 mo' when elapsed >2191 then 'too old' when elapsed between 180 and 270 then '6 mo' when elapsed between 271 and 365 then '9 mo'
when elapsed between 366 and 456 then '12 mo' when elapsed between 457 and 548 then '15 mo'
when elapsed between 549 and 729 then '18 mo' when elapsed between 730 and 913 then '24 mo' when elapsed between 914 and 1095 then '30 mo'
when elapsed between 1096 and 1461 then '3 yo' when elapsed between 1462 and 1826 then '4 yo' when elapsed between 1827 and 2191 then '5 yo' else   'not classed' end as classif
from
(
select cptcode, date1, birthdat, timestampdiff(day, birthdat,date1) as elapsed from archive_transactions
inner join register on register.patno = archive_transactions.patno
where date1 between :start_date and :end_date and cptcode in ('99391','99392','99393','99381','99382','99383')
and archive_flag = 1
) a
) t1 group by classif