Finding the average escalation resolution time for every coder by month

Question

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

Answer 1

Hi @Hellothere8028 ,

Please refer below dynamic way:

drop table if exists #temp  
  
;with cte as (  
select raisedby,year(raised) as y, left(DATENAME(month,raised),3) as m, raised,case when cast(raised as time)<cast(resolved as time) then  
cast(REPLACE(LEFT(CAST(DATEADD(SECOND, cast(DATEDIFF(MINUTE,cast(raised as time),cast(resolved as time)) as float)/60 * 3600, 0) AS TIME),5),':','.') as float)  
else cast(REPLACE(LEFT(CAST(DATEADD(SECOND, cast(DATEDIFF(MINUTE,cast(raised as time),dateadd(hour,24,cast(resolved as time))) as float)/60 * 3600, 0) AS TIME),5),':','.') as float)  
end diff from ##input)  
select raisedby,y,m,m+cast(y as char(4)) ym,   
count(raised) as raised,   
sum(case when diff is null then 1 else 0 end) tobe,  
AVG(diff) avg  
into #temp  
from cte  
group by raisedby,y, m  
  
declare @sql nvarchar(max)=''  
declare @s nvarchar(max)=''  
  
SELECT @s=STUFF(( SELECT ' max(case when ym = ''' + ym +''' then raised end) as numberofescalationsraisedin'+ym  
+',max(case when ym = ''' + ym +''' then avg end) as averagetimetaketoresolveescalationsraisedin'+ym+','  
FROM (select distinct ym from #temp) a  
FOR XML PATH('') ), 1, 1, '')   
  
select @s=SUBSTRING(@s,1,len(@s)-1)  
  
set @sql ='  
select raisedby personraised,  
max(tobe) as numberofescalationspendingtoberesolved,  
'+@s+'  
from #temp  
group by raisedby  
order by raisedby desc'  
  
EXECUTE sp_executesql  @sql  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Hellothere8028 ,

Please refer below and check whether it is helpful to you.

;with cte as (  
select raisedby,year(raised) as y,  left(DATENAME(month,raised),3) as m, raised,case when cast(raised as time)<cast(resolved as time) then  
cast(REPLACE(LEFT(CAST(DATEADD(SECOND, cast(DATEDIFF(MINUTE,cast(raised as time),cast(resolved as time)) as float)/60 * 3600, 0) AS TIME),5),':','.') as float)  
else cast(REPLACE(LEFT(CAST(DATEADD(SECOND, cast(DATEDIFF(MINUTE,cast(raised as time),dateadd(hour,24,cast(resolved as time))) as float)/60 * 3600, 0) AS TIME),5),':','.') as float)  
end diff from ##input)  
,cte1 as (  
select raisedby,m+cast(y as char(4)) ym,   
count(raised) as raised,   
sum(case when diff is null then 1 else 0 end) tobe,  
AVG(diff) avg  
from cte  
group by raisedby,y, m)  
select raisedby personraised,  
max(tobe) as numberofescalationspendingtoberesolved,  
max(case when ym = 'Nov2020' then raised end) as numberofescalationsraisedinnov2020,  
max(case when ym = 'Nov2020' then avg end) as averagetimetaketoresolveescalationsraisedinNov2020,  
max(case when ym = 'Dec2020' then raised end) as numberofescalationsraisedindec2020,  
max(case when ym = 'Dec2020' then avg end) as averagetimetakentoresolveescalationsraisedindec2020  
from cte1  
group by raisedby  
order by raisedby desc  

Output:

personraised	numberofescalationspendingtoberesolved	numberofescalationsraisedinnov2020	averagetimetaketoresolveescalationsraisedinNov2020	numberofescalationsraisedindec2020	averagetimetakentoresolveescalationsraisedindec2020  
amo77	0	1	10.26	1	3.12  
amo66	1	2	1.43	1	13.16  
amo54	1	3	8.675	2	9.285  

My average part of output is a little different from yours. Please kindly provide more details about your average part of output so that I could modify my query accordingly.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.