August 9, 2016 at 4:04 pm
,case when sum([CallsAnswered]) = 0 then 0 else sum([TalkTime]+[HoldTime]+[PostCallProcessingTime])/sum([CallsAnswered]) end as [IB HandleTime (Sec)]
TalkTime, holdtime, postcallprocessingTime are all sum of seconds after dividing the sum of those, into the calls answered to get handle time, instead of getting 1300 seconds, how would I convert this to h:mm:ss? 1300 seconds = 0:21:40
August 9, 2016 at 4:32 pm
As long as you're under 24 hours you can use the following.
DECLARE @duration_sec INT = 1300
SELECT DATEADD(SECOND, @duration_sec, CAST('00:00' AS TIME))
This method should work for any unit of time as long as the total duration is under 24 hours. If your duration is 24 hours or more, it becomes a bit more complicated.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 9, 2016 at 6:23 pm
Thanks for the help! So, the HandleTime will vary, depending on user -- all times will be less than 24 hours.
In he declare statement - is = 1300 hard coded?
August 9, 2016 at 6:27 pm
Where would I want to enter that?
SELECT
CONVERT (varchar,TimeStamp,1) AS 'DATE'
,cast([AgentLogin] as int) as EXT
,[AgentSurname] + ' ' + [AgentGivenName] + ' - ' + [AgentLogin] as '[Name & ext]'
,[AgentLogin] as Phone_EXT
,[AgentSurName]+', '+[AgentGivenName] as 'Agent Name'
,[SupervisorSurName]+', '+[SupervisorGivenName] as 'TeamLeader'
,[BusyOnDNTime]
,sum([CallsOffered]) as Calls_Off
,sum([CallsAnswered]) as Calls_Ans
,sum([HoldTime]) as HoldTime
,sum([TalkTime]) as TalkTime
,sum([PostCallProcessingTime]) as ACW
,sum([DNInExtCalls]) as Ext_In_Calls
,sum([DNInExtCallsTalkTime]) as Ext_In_Calls_TalkTime
,sum([DNInExtCallsHoldTime]) as Ext_In_Calls_HoldTime
,sum([DNInIntCalls]) as Int_In_Calls
,sum([DNInIntCallsTalkTime]) as Int_In_Calls_TalkTime
,sum([DNOutExtCalls]) as OutBound_Calls
,Sum([DNOutExtCallsTalkTime]) as OutBound_TalkTime
,sum([DNOutExtCallsHoldTime]) as OutBound_HoldTime
,sum([DNOutIntCalls]) as Interncal_OB_Calls
,sum([DNOutIntCallsTalkTime]) as Internal_OB_Calls_TalkTime
,sum([LoggedInTime]) as LoggedInTime
,sum([NotReadyTime]) as NotReadyTime
,[Time] as Interval
,sum([DNOutExtCallsHoldTime]) as OB_Calls_Hold_Time
,sum([DNOutIntCallsHoldTime]) as Internal_calls_hold_time
,sum([NumberTimesNotReady]) as Time_Entered_NotReady
,case when sum([CallsAnswered]) = 0 then 0 else sum([TalkTime]+[HoldTime]+[PostCallProcessingTime])/sum([CallsAnswered]) end as [IB HandleTime]
,case when sum([DNOutExtCalls]) = 0 then 0 else sum([DNOutExtCallsTalkTime]+[DNOutExtCallsHoldTime])/sum([DNOutExtCalls]) end as [OB HandleTime (sec)]
,case when sum([LoggedInTime]) = 0 then 0 else sum([NotReadyTime])/ CAST(sum([LoggedInTime])as money) end as [% of NotReady Time]
,case when sum([DNOutExtCallsTalkTime])= 0 then 0 else (sum([NotReadyTime])-sum([DNOutExtCallsTalkTime])-sum([DNOutExtCallsHoldTime]))/ CAST(sum(LoggedInTime) as money) end as [% of Unproductive Time]
,case when sum([NumberTimesNotReady]) = 0 then 0 else sum([NotReadyTime])/sum([NumberTimesNotReady]) end as [Avg Time not ready]
,case when sum([LoggedInTime]) = 0 then 0 else (sum([LoggedInTime]) -sum([TalkTime]+[HoldTime]+[PostCallProcessingTime]+[DNOutExtCallsTalkTime]+[DNOutExtCallsHoldTime]+[DNInExtCallsTalkTime]+[DNInExtCallsHoldTime])) *100/cast(sum([LoggedInTime]) as money) end as [(%) Of Unproductive time]
FROM [LDW_Avaya].[dbo].[dAgentPerformanceStat]
WHERE [AgentGivenName] not like '%test%'
GROUP BY [AgentLogin]
,[AgentSurName]
,[AgentGivenName]
,[SupervisorSurName]
,[SupervisorGivenName]
,[BusyOnDNTime]
,[TimeStamp]
,[Time]
ORDER BY 3 .
August 10, 2016 at 6:51 am
To use the formula, just change the variable with your "seconds" value. My guess is that you need to replace @Duration_Sec with sum([TalkTime]+[HoldTime]+[PostCallProcessingTime])/sum([CallsAnswered]).
August 10, 2016 at 6:58 am
drew.allen (8/9/2016)
As long as you're under 24 hours you can use the following.
DECLARE @duration_sec INT = 1300
SELECT DATEADD(SECOND, @duration_sec, CAST('00:00' AS TIME))
This method should work for any unit of time as long as the total duration is under 24 hours. If your duration is 24 hours or more, it becomes a bit more complicated.
Drew
Just if someone is interested on the version for over 24hours.
DECLARE @duration_sec INT = 354132502
SELECT STUFF( CONVERT(char(8), DATEADD(SECOND, @duration_sec, 0), 108), 1, 2, @duration_sec / 3600)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply