About

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.

Copy

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