This report looks for active patients under the age of 60 days old (at the time the report is run) who do not have a future appointment (of any kind) scheduled. It also lists if patients have had a recent visit in the office. This can be used as a transition of care tracking report for PCMH.
A sample image of this SQL report run in the Database Viewer is shown below:
To highlight and copy the code below to your clipboard, simply click the Copy button.
select register.patno,fname,lname,birthdat, recent_visit, sched_next from register left outer join (select patno scpat, max(appt_date) recent_visit from schedule where schedule.visit_status = 'Completed' and patno >= 99 group by patno order by patno) s1 on s1.scpat = register.patno left outer join (select patno, min(appt_date) sched_next, visit_status svs from schedule where appt_date >= cast('now' as date) and (schedule.visit_status not in ('Completed','Cancelled') or schedule.visit_status is null) and patno >= 101 group by patno, svs order by patno) f on f.patno = register.patno where status_pat = 'ACTIVE' and cast('now' as date)-birthdat < 60 and sched_next is null