Seconds to hours??

Question

Seconds to hours??

bcrockett

SSC Veteran

Points: 225
More actions
August 25, 2016 at 11:45 am

#316665

I am pulling data from an Avaya system,
What I am trying to do. Example, I am pulling from [dAgentBySkillsetStat].[TotalStaffedTime] which is the total time in seconds, of staffedtime
The value I get for example, will be 29732 which is seconds - how would I divide this by /86399 then multiply by*24 to get the time in hours (dec)
,[dAgentBySkillsetStat].[TotalStaffedTime]/ (86400*24 as decimal(18,2)) [Hrs.StaffedTime]
USE LDW_Avaya
GO
WITH A AS (
SELECT
[TimeStamp]
,[AgentLogin]
,SUM([DNOutExtCalls]) as [OutBound_Calls]
,SUM([DNOutExtCallsTalkTime]) AS [OutBoundHandleTime]
FROM [LDW_Avaya].[dbo].[dAgentPerformanceStat]
GROUP by [AgentLogin],[TimeStamp]
)
,B AS (
Select
[TimeStamp]
,[AgentLogin]
,ISNULL(SUM([ActivityTime]),0) as [OffLineTime]
FROM [LDW_Avaya].[dbo].[DActivityCodeStat]
WHERE [ActivityCodeName] IN ('Offline TL or OM Permission','Offline EMail','Offline_CSM','Offline BPO')
GROUP BY [TimeStamp],[AgentLogin]
)
SELECT
CONVERT (varchar,[dAgentBySkillsetStat].TimeStamp,1) AS 'DATE'
,CAST([dAgentBySkillsetStat].[AgentLogin] as Int) as EXT
,[dAgentBySkillsetStat].[AgentSurname] + ' ' + [dAgentBySkillsetStat].[AgentGivenName] as [AgentName]
,ISNULL(SUM([dAgentBySkillsetStat].[CallsOffered]),0) AS 'CallOffered'
,ISNULL(SUM([dAgentBySkillsetStat].[CallsAnswered]),0) as 'CallsAnswered'
,ISNULL(SUM([dAgentBySkillsetStat].[PostCallProcessingTime]),0) as 'ACW'
,ISNULL(SUM([dAgentBySkillsetStat].[ContactTalkTime]),0) as 'ContactTime'
,a.[OutBound_Calls]
,a.[OutBoundHandleTime]
,ISNULL(b.[OffLineTime],0) AS [Productive_OffLine]
,[dAgentBySkillsetStat].[NotReadyTime]
,[dAgentBySkillsetStat].[TotalStaffedTime]
,[dAgentBySkillsetStat].[IdleTime]
,SUM(isnull([ContactTalkTime],0)+isnull([PostCallProcessingTime],0)+isnull([DNOutExtTalkTime],0)) as [Total_Talk_Time]
,(isnull(A.[OutBound_Calls],0)+isnull(sum([dAgentBySkillsetStat].[CallsAnswered]),0)) AS [Total_Calls]
,[dAgentBySkillsetStat].[TotalStaffedTime] /CAST(86400*24 as money) [Hrs.StaffedTime]
FROM [LDW_Avaya].[dbo].[dAgentBySkillsetStat]
LEFT JOIN A ON [dAgentBySkillsetStat].[AgentLogin] = A.[AgentLogin]
AND [dAgentBySkillsetStat].[TIMESTAMP] = A.[TIMESTAMP]
LEFT JOIN B ON [dAgentBySkillsetStat].[AgentLogin] = B.[AgentLogin]
AND [dAgentBySkillsetStat].[TIMESTAMP] = B.[TIMESTAMP]
WHERE 1=1
AND [Skillset] in
('Contractors','Existing_DT'
,'Homeowner','Open_DT'
,'Pod_1_sk','Pod_2_sk'
,'Pod_3_sk','Pod_4_sk'
,'Pod_5_sk','Pre_Season')
GROUP BY
[dAgentBySkillsetStat].[TimeStamp]
,[AgentSurname]
,[AgentGivenName]
,[dAgentBySkillsetStat].[AgentLogin]
,[IdleTime]
,[TotalStaffedTime]
,[NotReadyTime]
,a.[OutBound_Calls]
,a.[OutBoundHandleTime]
,b.[OffLineTime]
ORDER BY DATE DESC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I'm sorry, I'm lost. What's the problem that you have?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Y.B. SSChampion Points: 11649 More actions · Answer 2

This seems too easy so maybe I don't understand but if you merely want to convert seconds to hours you can just do something like this.

DECLARE @seconds INT = 29732

SELECT CAST (@seconds * 1.0 / 3600 AS DECIMAL (5,2)) AS StaffedTimeHours

EDIT: I added the variable to make it more clear.

SELECT quote FROM brain WHERE original = 1
0 rows returned

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

this article by Jeff Moden may give you some ideas

http://www.sqlservercentral.com/articles/T-SQL/103343/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

I'm wondering why you store staffed time in seconds. How much do you pay per second? 😛

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 5

The Dixie Flatline (8/25/2016)
I'm wondering why you store staffed time in seconds. How much do you pay per second? 😛

Been there and done that. It's the Avaya Phone System at work here. All calls are recorded in seconds and reporting is frequently done by summing the seconds (sometimes a rather HUGE number of seconds) and then converting them to decimal hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)