question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked ArunChandramouli-6978 commented

Finding the average escalation resolution time for every coder by month

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



sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What have you tried so far?

you want to get the time difference between raised and resolution - not sure what you want but either ignore them if you don't want to consider or use a default of current date to involve them (although won't necessarily be accurate as a recently opened case will give a favourable slant to an average but then do you really want to ignore unresolved that have been opened for months?)

create new column to hold your month - eomonth(raiseddate)

then group by new month column, coder and do your various aggregations

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978,

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ArunChandramouli-6978,

Could you please provide any update?

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·

My apologies for the late update @MelissaMa-msft ...Really appreciate your help!..This works!


Thanks,
Arun

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ArunChandramouli-6978 commented

Hi @ArunChandramouli-6978,

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MelissaMa-msft ...Really appreciate your response... The problem is that the average time diff for amo54 for Nov 2020 should be large because of this datapoint... ('19','438','11/26/2020 1:59','amo54','12/26/2020
8:00') ... Here the escalation got raised on November 26th 2020 and got resolved only on december 26th...Also is there a way to automatically generate the count of escalations and the average diff across months without specifying it by every month like in the code you had provided..


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

0 Votes 0 ·

Hi @ArunChandramouli-6978,

116164-avg.png

Per my understanding, we need to calculate the average of above two rows in red box.

Could you please provide the details of calculation of 6.18?

Also is there a way to automatically generate the count of escalations and the average diff across months without specifying it by every month like in the code you had provided..

In order to achieve above requirement, maybe dynamic statement is required.
if you are confirmed to proceed with it, I will update my query once the average of nov2020 worked.

Best regards,
Melissa

0 Votes 0 ·
avg.png (13.8 KiB)

Hi @MelissaMa-msft : Yes you are correct ....that's the average of rows that I meant.(My apologies the average of 6.18 is wrong) ...In fact the average will be huge...Really appreciate your response!...It would be great if you can help with the dynamic query....

0 Votes 0 ·