About

This SQL will show a comparison of previous year to current year week-by-week well and sick visits. It is based on the following assumptions:

• Since everyone uses different appointment type names, the query is based on the generic purpose with which the appointment type is associated. Well associates with purpose 4, which is "Preventive". Sick is associated with purposes 3 and 8, which mean "Non-Preventive" and "Telehealth" respectively. If a practice has not created these associations, they should either go to the first tab of the Appointment Types window and attach them, or they can change the query to include a list of their appointment type names that associate with Well and Sick visits.

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

SQL Code: Firebird

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

Copy

select year_num, week_num, well_2019, well_2020,
iif(well_2019 = 0, '', cast(round((well_2020 / well_2019) * 100)||'%' as varchar(20))) as well_pct,
sick_2019, sick_2020,
iif(sick_2019 = 0, '', cast(round((sick_2020 / sick_2019) * 100)||'%' as varchar(20))) as sick_pct
from (select year_num, week_num,
(select count(s.uniqkey) as appt_count
from schedule s
left join appt_type a on s.code1=a.code1
where s.patno > 0
and (a.stage=4)
and ((s.visit_status is null) or
     (s.visit_status not in ('Cancelled','No Show','No Show*')))
and s.appt_date between :start_date - 366 and :end_date - 366
and extract(year from s.appt_date) = year_num - 1
and extract(week from s.appt_date) = week_num) as WELL_2019,
(select count(s.uniqkey) as appt_count
from schedule s
left join appt_type a on s.code1=a.code1
where s.patno > 0
and (a.stage=4)
and ((s.visit_status is null) or
     (s.visit_status not in ('Cancelled','No Show','No Show*')))
and s.appt_date between :start_date and :end_date
and extract(year from s.appt_date) = year_num
and extract(week from s.appt_date) = week_num) as WELL_2020,
(select count(s.uniqkey) as appt_count
from schedule s
left join appt_type a on s.code1=a.code1
where s.patno > 0
and (a.stage in (3,9))
and ((s.visit_status is null) or
     (s.visit_status not in ('Cancelled','No Show','No Show*')))
and s.appt_date between :start_date - 366 and :end_date - 366
and extract(year from s.appt_date) = year_num - 1
and extract(week from s.appt_date) = week_num) as SICK_2019,
(select count(s.uniqkey) as appt_count
from schedule s
left join appt_type a on s.code1=a.code1
where s.patno > 0
and (a.stage in (3,9))
and ((s.visit_status is null) or
     (s.visit_status not in ('Cancelled','No Show','No Show*')))
and s.appt_date between :start_date and :end_date
and extract(year from s.appt_date) = year_num
and extract(week from s.appt_date) = week_num) as SICK_2020
from (select distinct extract(week from s.appt_date) as week_num,
extract(year from s.appt_date) as year_num
from schedule s where s.appt_date between :start_date and :end_date) as weeks) as appts

SQL Code: MySQL

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

Copy

select s1.year_num,  s1.week_num, 
wells_last_yr.appt_count_well as well_last_yr,
wells_now.appt_count_well as well_this_yr,
case when wells_last_yr.appt_count_well = 0 then '' else (round((wells_now.appt_count_well * 100 / wells_last_yr.appt_count_well),1 ) || ' %') end as well_pct,

sicks_last_yr.appt_count_sick as sick_last_yr, 
sicks_now.appt_count_sick as sick_this_yr,
case when sicks_last_yr.appt_count_sick = 0 then '' else (round((sicks_now.appt_count_sick * 100 /sicks_last_yr.appt_count_sick),1 ) || ' %') end as sick_pct
 from
 (select distinct extract(year from appt_date) as year_num, extract(week from appt_date) as week_num  from schedule where
appt_date between :start_date and :end_date) s1
left outer join (
select count(s.uniqkey) as appt_count_sick,  extract(week from s.appt_date) as week_num
from schedule s left join appt_type a on s.code1=a.code1
where s.patno > 0 and (a.stage in (3,9))
and ((s.visit_status is null) or (s.visit_status not in ('Cancelled','No Show','No Show*')))
and ( s.appt_date between :start_date and :end_date)
group by week_num
) sicks_now on  sicks_now.week_num = s1.week_num
left outer join
(
select count(s.uniqkey) as appt_count_sick, extract(week from s.appt_date) as week_num
from schedule s left join appt_type a on s.code1=a.code1
where s.patno > 0 and (a.stage in (3,9))
and ((s.visit_status is null) or (s.visit_status not in ('Cancelled','No Show','No Show*')))
and (s.appt_date between date_add(cast(:start_date as date), interval -366 day) and date_add(cast(:end_date as date),interval -366 day))
group by  week_num
) sicks_last_yr on sicks_last_yr.week_num = s1.week_num
left outer join
(
select count(s.uniqkey) as appt_count_well,  extract(week from s.appt_date) as week_num
from schedule s left join appt_type a on s.code1=a.code1
where s.patno > 0 and (a.stage in (4))
and ((s.visit_status is null) or (s.visit_status not in ('Cancelled','No Show','No Show*')))
and ( s.appt_date between :start_date and :end_date)
group by week_num
) wells_now on wells_now.week_num = s1.week_num

left outer join
(
select count(s.uniqkey) as appt_count_well,  extract(week from s.appt_date) as week_num
from schedule s left join appt_type a on s.code1=a.code1
where s.patno > 0 and (a.stage in (4))
and ((s.visit_status is null) or (s.visit_status not in ('Cancelled','No Show','No Show*')))
and (s.appt_date between date_add(cast(:start_date as date), interval -366 day) and date_add(cast(:end_date as date),interval -366 day) )
group by week_num
) wells_last_yr on wells_last_yr.week_num = s1.week_num