About
Based on a schedule date range, this shows how many full days, AM half days, and PM half days each of your clinicians worked.
A sample image of this SQL report run in the Database Viewer is shown below:
Caveats
- This uses every column on your schedule that has appointments in it, including if, for example, you have columns marked “Nurse Visit,” “Overflow,” etc.
- This only considers appointments whose visit status is marked Completed (i.e. not No Show or Cancelled).
- If the last appointment on a provider's schedule for the day is before noon, it is an "am day"
- If the first appointment on a provider's schedule is after noon, it is a "pm day"
- If the provider saw both morning and afternoon appointments, it is a "full day" (even if your provider's first appt was at 11:30 and their last one was at 12:30)
Code
To highlight and copy the code below to your clipboard, simply click the Copy button.
with halfdays as ( select b.*, case when am_work = 'worked in am' and pm_work = 'worked in pm' then 'full day' when am_work = 'worked in am' then 'worked in am' when pm_work = 'worked in pm' then 'worked in pm' else 'neither' end as schedule_dispo from ( select a.*, case when first_appt < dateadd(minute,720,appt_date) then 'worked in am' else 'nope' end as am_work, case when last_appt > dateadd(minute,720,appt_date) then 'worked in pm' else 'nope' end as pm_work from ( select scheduled_provider, min(my_appt_time) as first_appt, max(my_appt_time) as last_appt, appt_date from ( select staffname as scheduled_provider, dateadd(minute,start_time, appt_date) as my_appt_time, appt_date, visit_status from schedule inner join staff1 on staff1.staffid = schedule.addr_id where appt_date between :start_date and :end_date and visit_status = 'Completed' and patno is not null ) group by appt_date, scheduled_provider ) a ) b ), providers as (select distinct scheduled_provider from halfdays) select providers.scheduled_provider, full_days, am_days,pm_days from providers left outer join (select scheduled_provider, count(schedule_dispo) as full_days from halfdays where schedule_dispo = 'full day' group by scheduled_provider) f on f.scheduled_provider = providers.scheduled_provider left outer join (select scheduled_provider, count(schedule_dispo) as am_days from halfdays where schedule_dispo = 'worked in am' group by scheduled_provider) aa on aa.scheduled_provider = providers.scheduled_provider left outer join (select scheduled_provider, count(schedule_dispo) as pm_days from halfdays where schedule_dispo = 'worked in pm' group by scheduled_provider) pp on pp.scheduled_provider = providers.scheduled_provider