About

This SQL creates a report that helps you identify patients who were a certain age range during a period you specify. It also indicates patient status, primary, and secondary insurance.

For example, at runtime, you might enter start dates and end dates of 1/1/2017 and 12/31/2017. You will see your entire patient roster, along with the age in months they were on 1/1/2017 (the AGE AT START) and the age in months they were on 12/31/2017 (the AGE END DATE). Some patients will have negative ages (i.e. they weren’t born yet in 2017).

You can then use the dropdown filters to see only the patients who meet specified criteria.  For example, if you only want to see active patients with Medicaid, you would change the STATUS_PAT dropdown to ACTIVE and set the INS_CARRIER_CODE (primary insurance) and/or INS2_CARRIER_CODE (secondary insurance) fields to show the insurances that reflect Medicaid in your area.  You can also change the AGE AT START or AGE AT END to only show certain age groups you specify.

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

Caveats

• This report shows all patient statuses, although you can set the dropdown filter only to ACTIVE when you wish.
• The ages shown are ages in MONTHS.  For example, a patient with an age listed as 120 would just have turned 10 years old (12 months x 10 years).

SQL Code: Firebird

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

Copy

select * from (
select patno, fname, lname, birthdat, 
datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,:start_date)) as age_at_start,

datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,:end_date)) as age_at_end,
  status_pat, ins_carrier_code, ins2_carrier_code from register
where patno >99 order by patno
)

SQL Code: MySQL

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

Copy

select * from (
select patno, fname, lname, birthdat, 

floor(timestampdiff(year,birthdat,:start_date)) as age_at_start,
floor(timestampdiff(year,birthdat,:end_date)) as age_at_end,

  status_pat, ins_carrier_code, ins2_carrier_code from register
where patno >99 order by patno
) t1