Finding the average escalation resolution time for every coder by month

Transact-SQL
1

Hi All,

I am trying to find the average resolution time in hours and minutes for every coder by escalation raised month... An escalation is denoted by a combination of ctextid and vbill...For escalations that are not resolved (denoted by NULL) the average time calculations should not be taken into account for those.. Raised is the datetimestamp when the escalations got raised; Resolved is the datetimestamp when the escalations got resolved.Please find the DDL for the input and output tables...Can you please help me here..

Input table

create table ##input
(ctext int,
vbill int,
raisedby varchar(20),
raised datetime2,
resolved datetime2)

insert into ##input values
('12','431','11/23/2020 0:41','amo77','11/23/2020 11:07'),
('13','432','12/3/2020 6:47','amo77','01/7/2021 9:59'),
('14','433','11/19/2020 5:12','amo66','11/19/2020 6:55'),
('15','434','11/9/2021 6:00','amo66','NULL'),
('16','435','12/24/2020 22:00','amo66','12/25/2020 11:16'),
('17','436','11/24/2020 21:01','amo54','11/25/2020 8:35'),
('18','437','12/2/2020 21:58','amo54','12/3/2020 7:25'),
('19','438','11/26/2020 1:59','amo54','12/26/2020 8:00'),
('20','439','11/24/2020 23:19','amo54','NULL'),
('21','440','12/29/2020 23:32','amo54','01/15/2021 9:02')

Output table

create table ##output
(personraised varchar(20),
numberofescalationspendingtoberesolved int,
numberofescalationsraisedinnov2020 int,
averagetimetaketoresolveescalationsraisedinNov2020 float,
numberofescalationsraisedindec2020 int,
averagetimetakentoresolveescalationsraisedindec2020 float)

insert into ##output values
('amo77','','1','10.26','1','3.11'),
('amo66','1','2','1.43','1','13.15'),
('amo54','1','3','6.18','2','9.28'),

Thanks,
Arun

2

Very nice sample data, but did you test it locally first? there are errors

3

Sample data is hosed, but not sure how your averages are working. Can you provide more insight into that? Here are the other calculations you asked for

create table #input
(ctext int,
vbill int,
raisedby varchar(20),
raised datetime2,
resolved datetime2)

insert into #input  (ctext, vbill, raised, raisedby, resolved)
values
('12','431','11/23/2020 0:41','amo77','11/23/2020 11:07'),
('13','432','12/3/2020 6:47','amo77','01/7/2021 9:59'),
('14','433','11/19/2020 5:12','amo66','11/19/2020 6:55'),
('15','434','11/9/2021 6:00','amo66',NULL),
('16','435','12/24/2020 22:00','amo66','12/25/2020 11:16'),
('17','436','11/24/2020 21:01','amo54','11/25/2020 8:35'),
('18','437','12/2/2020 21:58','amo54','12/3/2020 7:25'),
('19','438','11/26/2020 1:59','amo54','12/26/2020 8:00'),
('20','439','11/24/2020 23:19','amo54',NULL),
('21','440','12/29/2020 23:32','amo54','01/15/2021 9:02')


Select raisedby, 
	   sum(case when Resolved is null then 1 else 0 end) as numberofescalationspendingtoberesolved 
	   ,sum(case when Year(raised) = 2020 and Month(raised) = 11 then 1 else 0 end) as numberofescalationsraisedinnov2020 
	   ,sum(case when Year(raised) = 2020 and Month(raised) = 12 then 1 else 0 end) as numberofescalationsraisedindec2020 
from #input  
group by raisedby
4

Hi Mike, I am calculating the difference between raised and resolved in hours and then averaging it for all the escalations raised in that month by coder....

Thanks,
Arun

5

For escalations that are not resolved (resolved is null) those are excluded from the calculations...

6

I don't understand your expected results. If we look at November, there are 5 raised in Nov 2020. when I look at those raised by amo54, there are 3, but on is not resolved. The hours difference on the second on that is resolved is 727. So, how do you get 6.18??

7

Hi @mike01 : I am trying to find the average difference in hours for raised and resolved escalations by coder (Now for the count of escalations I am counting escalations even if they are not resolved and just raised...Yes apologies my data for amo54 for november is wrong

8

This looks right based on your description, but you'll have to let us know

Select raisedby, 
	   sum(case when Resolved is null then 1 else 0 end) as numberofescalationspendingtoberesolved 
	   ,sum(case when Year(raised) = 2020 and Month(raised) = 11 then 1 else 0 end) as numberofescalationsraisedinnov2020 
	   ,cast(sum(case when Year(raised) = 2020 and Month(raised) = 11 and resolved is not null 
				 then datediff(day, raised, resolved) 
		    else 0 end)/
		sum(case when Year(raised) = 2020 and Month(raised) = 11 and resolved is not null 
				 then 1.0 
			else 0 end) as Numeric(12,2)) as averagetimetaketoresolveescalationsraisedinNov2020
	   ,sum(case when Year(raised) = 2020 and Month(raised) = 12 then 1 else 0 end) as numberofescalationsraisedindec2020 
	   ,Cast(sum(case when Year(raised) = 2020 and Month(raised) = 12 and resolved is not null 
				 then datediff(day, raised, resolved) 
		    else 0 end)/
		sum(case when Year(raised) = 2020 and Month(raised) = 12 and resolved is not null 
				 then 1.0 
			else 0 end) as Numeric(12,2)) as averagetimetaketoresolveescalationsraisedinDec2020
from #input  
group by raisedby
1 Like
9

Thanks Mike!...Appreciate your help!