About

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

Caveats

1. Only shows completed appointments  
2. If an appointment was moved (as opposed to cancelled and  rebooked with a new appointment instance), it will show the time elapsed between the _original_ appointment scheduling time and the appointment, not when mom called back and said "Can I move that checkup back to next week?"
3. The diagnoses are shown so you can help decide how good your triage is.  For example, you might wait someone wait 3 weeks for a "sick" appointment.  You'll want to know if the final diagnosis was "influenza" (not acceptable with 3 week wait) or "ADHD" (acceptable for 3 week wait).  
4. Checkups may have long "wait times" if you do the right thing and schedule, e.g. the 4 month EPSDT when they leave the office after the 2 month EPSDT.  That's why it's broken out by types of appointments.  
5. It is helpful to see the "scheduled by" initials to look for patterns to see if certain staff members were having difficulty adhering to our schedule guidelines.

SQL Code: Firebird

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

Copy

select appt_made_date_time, appt_date_time, case when elapsed_days >=1 and elapsed_days <=9 then (elapsed_days || ' days') when elapsed_days >9 then (elapsed_weeks || ' weeks') 
when elapsed_days<1 and elapsed_hours>=1 then (elapsed_hours || ' hours') when elapsed_hours <1 then (elapsed_minutes || ' minutes')

 end as elapsed, patno, code1, staffname, loc_name, scheduled_by,d1,d2,d3,d4
from (
select time_scheduled as appt_made_date_time, my_appt_time as appt_date_time,  round(elapsed_time,2) as elapsed_days, round( elapsed_time*24,2) as elapsed_hours, round(elapsed_time*24*60,2) as elapsed_minutes, round(elapsed_time/7,2) as elapsed_weeks, patno, code1, staffname,  scheduled_by, d1,d2,d3,d4, loc_id
from (
select d.*, ( my_appt_time - time_scheduled) as elapsed_time
from (
select c.*, cast(new_date || ' ' || adj_hr || ':' || mn as timestamp) as time_scheduled
from
(
select b.*, (mo || '/' || dy || '/20' || yr) as new_date, case when (ampm = 'P' or ampm = 'PM') and hr<12 then round(12+hr,0) else hr end as adj_hr
from
(
select a.*, substring(scheduled_when from 1 for 2) as mo, substring(scheduled_when from 3 for 2) as dy, substring(scheduled_when from 5 for 2) as yr, substring(scheduled_when from 8 for 2) as hr, substring(scheduled_when from 11 for 2) as mn, substring(scheduled_when from 13 for 2) as ampm from
(
SELECT DISTINCT SCHEDULE_1.appt_date ,schedule_1.patno, dateadd(minute,start_time,appt_date) as my_appt_time, schedule_1.name1, schedule_1.loc_id,
                SCHEDULE_1.CODE1, 
                SCHEDULE_1.ARRIVALTIME,( arrivaltime - dateadd(minute,start_time,appt_date))*24 as elapsed,
                staff1.staffname,
                d1, d2,d3,d4, substring(name1 from 1 for position('[' in schedule_1.name1)-1) as scheduled_by,  substring(name1 from position(']' in schedule_1.name1)+1 for 20) as scheduled_when
FROM SCHEDULE SCHEDULE_1
left outer join
(select patno, date1, max(dxname1) as d1,max(dxname2) as d2,max(dxname3) as d3,max(dxname4) as d4 from archive_transactions where dxname1 is not null group by patno, date1) at1 on (at1.PATNO = SCHEDULE_1.PATNO) and at1.date1 = schedule_1.appt_date
   inner join staff1 on staff1.staffid = schedule_1.addr_id
WHERE ( SCHEDULE_1.APPT_DATE = :my_date )
       AND ( SCHEDULE_1.PATNO <> 0 )
       AND ( at1.DATE1 = :my_date )

and visit_status = 'Completed'
ORDER BY SCHEDULE_1.CODE1, SCHEDULE_1.PATNO
) a  )b ) c  )d
)e ) f
inner join location on location.id = f.loc_id

SQL Code: MySQL

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

Copy

select appt_made_date_time, appt_date_time, case when elapsed_days >=1 and elapsed_days <=9 then (elapsed_days || ' days') when elapsed_days >9 then (elapsed_weeks || ' weeks') 
when elapsed_days<1 and elapsed_hours>=1 then (elapsed_hours || ' hours') when elapsed_hours <1 then (elapsed_minutes || ' minutes')
 end as elapsed, patno, code1, staffname, scheduled_by, d1,d2,d3,d4
from (
select time_scheduled as appt_made_date_time, my_appt_time as appt_date_time,elapsed_days,elapsed_hours,elapsed_minutes,  elapsed_weeks, patno, code1, staffname,  scheduled_by, d1,d2,d3,d4
from (
select d.*,  timestampdiff(minute,time_scheduled, my_appt_time) as elapsed_minutes,  timestampdiff(hour,time_scheduled, my_appt_time) as elapsed_hours,  timestampdiff(day,time_scheduled, my_appt_time) as elapsed_days,
 timestampdiff(week,time_scheduled, my_appt_time) as elapsed_weeks
from (
select c.*, cast((new_date || ' ' || adj_hr || ':' || mn || ':00' ) as datetime) as time_scheduled
from
(
select b.*, ('20' || yr || '-' || mo || '-' || dy ) as new_date, case when (ampm = 'P' or ampm = 'PM') and hr<12 then round(12+hr,0) else hr end as adj_hr
from
(
select a.*, cast(scheduled_when as datetime) as dt, substring(scheduled_when from 1 for 2) as mo, 
substring(scheduled_when from 3 for 2) as dy, substring(scheduled_when from 5 for 2) as yr, substring(scheduled_when from 8 for 2) as hr, substring(scheduled_when from 11 for 2) as mn, 
substring(scheduled_when from 13 for 2) as ampm from
(
SELECT DISTINCT SCHEDULE_1.appt_date ,schedule_1.patno, date_add(appt_date,interval start_time minute) as my_appt_time, schedule_1.name1,
                SCHEDULE_1.CODE1, 
                SCHEDULE_1.ARRIVALTIME,
                staff1.staffname,
                d1, d2,d3,d4, substring(name1 from 1 for position('[' in schedule_1.name1)-1) as scheduled_by,  substring(name1 from position(']' in schedule_1.name1)+1 for 20) as scheduled_when
FROM SCHEDULE SCHEDULE_1
left outer join
(select patno, date1, max(dxname1) as d1,max(dxname2) as d2,max(dxname3) as d3,max(dxname4) as d4 from archive_transactions where dxname1 is not null group by patno, date1) at1 
on (at1.PATNO = SCHEDULE_1.PATNO) and at1.date1 = schedule_1.appt_date
   inner join staff1 on staff1.staffid = schedule_1.addr_id
WHERE ( SCHEDULE_1.APPT_DATE = :ref_date )
       AND ( SCHEDULE_1.PATNO <> 0 )
       AND ( at1.DATE1 = :ref_date )
and visit_status = 'Completed'
ORDER BY SCHEDULE_1.CODE1, SCHEDULE_1.PATNO
) a  )b ) c  )d ) e ) f