Report On Number Of Incidents Assigned To A Group

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

]Good Day,

I Managed to write my report, I have attached the result set for those would like to make use of somethhing similar in the future, below is the query:



declare @StartDate Datetime
declare @EndDate Datetime

set @StartDate = '2019-08-28'
set @EndDate = getdate()

select
convert(varchar,sr.[insert_time],111) as 'Logged Date',
convert(varchar,t.[Escelated Date],111) as 'Escalation Date',
convert(varchar,sr.close_time,111) as 'Resolved Date',
convert(varchar(7),t.[Escelated Date],126) as 'Escalation month',
sr.id,
sr.request_user as 'Request User',
c.company_name,
case sr.sr_type
when 1 then 'Incident'
when 10 then 'Request'
Else 'Unknown'
end as Type,
sr.problem_type as 'Category',
sr.problem_sub_type as 'Sub Category',
sr.responsibility as 'Assigned User',
sr.assigned_group as 'Assigned Group',
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'
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
left join sysaid_user su on su.user_name = sr.request_user
left join company c on c.company_id = su.company
where [Escelated Date] between @StartDate and @EndDate +1
--where sr.id = 313713
group by
convert(varchar,t.[Escelated Date],111), t.[Escelated Date],
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,
sr.id, insert_time, close_time, request_user, c.company_name, sr.sr_type, sr.responsibility, sr.assigned_group, sr.problem_type , sr.problem_sub_type
order by [Escalation Date], id