Inbox / Follow up actions SQL search BP

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