Good day,
We have three Major Admin Groups nml. Support, Technical, Branches
Support handles all incoming calls and then for third line support will "escalate" the incident to technical.
I need to report on how many incidents and requests are escalated to Technical. How would I go about tracking this?
I did manage to pull information from the Service_Req_History Table by making use of the following:
select
convert(varchar,t.[Escelated Date],111) as 'Escalation Date',
case when s.[Support Last Update] is null then 'No' Else 'Yes'
End as 'SupportEscalated',
case
when b.[Branch Last Update] is null then 'No' Else 'Yes'
End as 'BranchEscalated',
count(*) as 'Total Escalations'
from service_req sr
join (select id, max(update_time) as 'Support Last Update' from service_req_history sh
where sh.assigned_group = 'Support' group by id) S on s.id = sr.id
join (select id, min(update_time) as 'Escelated Date' from service_req_history sh
where sh.assigned_group = 'Technical' and version not in (1)group by id) T on t.id = sr.id
left join (select id, max(update_time) as 'Branch Last Update' from service_req_history sh
where sh.assigned_group = 'Branch' group by id) B on b.id = sr.id
where [Escelated Date] > '2019-07-25'
group by
convert(varchar,t.[Escelated Date],111),
case when b.[Branch Last Update] is null then 'No' Else 'Yes' end,
case when s.[Support Last Update] is null then 'No' Else 'Yes' end
Below is the result set:
Escalation Date SupportEscalated BranchEscalated Total Escalations
2019/07/25 Yes No 25
2019/07/26 Yes No 23
2019/07/27 Yes No 1
2019/07/29 Yes No 9
I am not sure if this is the only/best way to go about this. Any input would be highly appreciated.
Thanking you in advance
Kind Regards
We have three Major Admin Groups nml. Support, Technical, Branches
Support handles all incoming calls and then for third line support will "escalate" the incident to technical.
I need to report on how many incidents and requests are escalated to Technical. How would I go about tracking this?
I did manage to pull information from the Service_Req_History Table by making use of the following:
select
convert(varchar,t.[Escelated Date],111) as 'Escalation Date',
case when s.[Support Last Update] is null then 'No' Else 'Yes'
End as 'SupportEscalated',
case
when b.[Branch Last Update] is null then 'No' Else 'Yes'
End as 'BranchEscalated',
count(*) as 'Total Escalations'
from service_req sr
join (select id, max(update_time) as 'Support Last Update' from service_req_history sh
where sh.assigned_group = 'Support' group by id) S on s.id = sr.id
join (select id, min(update_time) as 'Escelated Date' from service_req_history sh
where sh.assigned_group = 'Technical' and version not in (1)group by id) T on t.id = sr.id
left join (select id, max(update_time) as 'Branch Last Update' from service_req_history sh
where sh.assigned_group = 'Branch' group by id) B on b.id = sr.id
where [Escelated Date] > '2019-07-25'
group by
convert(varchar,t.[Escelated Date],111),
case when b.[Branch Last Update] is null then 'No' Else 'Yes' end,
case when s.[Support Last Update] is null then 'No' Else 'Yes' end
Below is the result set:
Escalation Date SupportEscalated BranchEscalated Total Escalations
2019/07/25 Yes No 25
2019/07/26 Yes No 23
2019/07/27 Yes No 1
2019/07/29 Yes No 9
I am not sure if this is the only/best way to go about this. Any input would be highly appreciated.
Thanking you in advance
Kind Regards
This message was edited 1 time. Last update was at Jul. 29, 2019 09:10 AM