About

This SQL shows patients who are behind on the AAP schedule of well-child care visits. It produces information similar to the DAR, with the added advantage that you can see how far behind a patient might be (13 months past their last annual checkup vs never had a checkup).

Caveats

None

Reports Usage Ideas

You might notice an exceptionally high number of patients that are past due for well visits. This SQL includes all active patients, so if you haven’t inactivated patients who have aged out of the practice or haven’t had a visit in years, your number and percentage of past due patients will look very high. This a good reminder to inactivate patients who are no longer in the practice, or try to recapture as many as possible.

SQL Code: Firebird

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

Copy

select d.* from
(
select c.*, appt_date, code1  from
(
select b.* from
(
select a.*, 
case when months_ago_checkup is null then 'due' 
when months_ago_checkup >=12 then 'due' 
when months_ago_checkup >=6 and months_old >= 20.75 and months_old <35.5 then 'due'  
when months_ago_checkup >=3 and months_old >= 8.75  and months_old <20.75 then 'due' 
when months_ago_checkup >=3 and months_old >= 7.75 and months_old <8.75 then 'due'
when months_ago_checkup >=2 and months_old >=1.75  and months_old <7.75 then 'due'
when months_ago_checkup >=1 and months_old <1.75 then 'due'
else 'ok'  end as due
from (
select register.patno, fname, lname, register.birthdat, (datediff(month, dateadd(day,-extract(day from birthdat)+1,birthdat), dateadd(day,-extract (day from birthdat)+1,cast('today' as date)))) as months_old,  last_pe, floor(datediff(month, dateadd(day,-extract(day from last_pe)+1,last_pe), dateadd(day,-extract (day from last_pe)+1,cast('today' as date)))) as months_ago_checkup from register
left outer join (select patno, max(date1) as last_pe from physical group by patno) p on p.patno = register.patno
where status_pat = 'ACTIVE' and register.patno >99 and lname <> 'TESTPATIENT') a
) b 
) c
left outer join (select * from schedule where appt_date >= cast('today' as date) and ((visit_status <> 'Cancelled' and visit_status not like 'No Show%') or visit_status is null)) s on s.patno = c.patno
) d

SQL Code: MySQL

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

Copy

select d.* from
(
select c.*, appt_date, code1  from
(
select b.* from
(
select a.*,
case when months_ago_checkup is null then 'due'
when months_ago_checkup >=12 then 'due'
when months_ago_checkup >=6 and months_old >= 20.75 and months_old <35.5 then 'due'  
when months_ago_checkup >=3 and months_old >= 8.75  and months_old <20.75 then 'due'
when months_ago_checkup >=3 and months_old >= 7.75 and months_old <8.75 then 'due'
when months_ago_checkup >=2 and months_old >=1.75  and months_old <7.75 then 'due'
when months_ago_checkup >=1 and months_old <1.75 then 'due'
else 'ok'  end as due
from (
select register.patno, fname, lname, register.birthdat,
floor(timestampdiff(month,birthdat,curdate())) as months_old,
 last_pe,
 floor(timestampdiff(month,last_pe,curdate())) as months_ago_checkup
 from register
left outer join (select patno, max(date1) as last_pe from physical group by patno) p on p.patno = register.patno
where status_pat = 'ACTIVE' and register.patno >99 and lname <> 'TESTPATIENT') a
) b
) c
left outer join (select * from schedule where appt_date >= curdate() and ((visit_status <> 'Cancelled' and visit_status not like 'No Show%') or visit_status is null)) s on s.patno = c.patno
) d