Report On Number Of Incidents Assigned To A Group

 
Author
Message
SysAider
20
 
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

This message was edited 1 time. Last update was at Jul. 29, 2019 09:10 AM

SysAider
20
 
]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