NB: for specific information on just Non urgent recalls searches- see the article:
- https://appointuit.zendesk.com/hc/en-us/articles/214202068
select
[BPSPatients].[dbo].bpspayee(checkedby) as Provider
, sum(F) as 'Urgent Recall'
, sum(E) as 'Non-Urgent Recall'
, sum(D) as 'Send Routine Reminder'
, sum(C) as 'Doctor to Advise'
, sum(B) as 'Nurse to Advise'
, sum(A) as 'Reception to Advise'
from
(
SELECT checkedby
, case
when action = 7 then count(action)
else 0
end as F --'Urgent Recall'
, case
when action = 6 then count(action)
else 0
end as E --'Non-Urgent Recall'
, case
when action = 5 then count(action)
else 0
end as D --'Send Routine Reminder'
, case
when action = 4 then count(action)
else 0
end as C --'Doctor to Advise'
, case
when action = 3 then count(action)
else 0
end as B --'Nurse to advise'
, case
when action = 2 then count(action)
else 0
end as A -- 'Reception to Advise'
FROM [BPSPatients].[dbo].[INVESTIGATIONS]
where action >1
and Investigations.Actioned = 0
and investigations.recordstatus = 1
group by checkedby, Action
) as source
group by checkedby