Return time from seconds - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Return time from seconds

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-19 : 12:14:33

Hi

can someone help me with this?

I'm trying to return total avg seconds as 00:00:00
Here's the code, I've used DatePart in the past but the 2 fields already subtract to seconds in this format 222,236,598...I need this to be a time format, so it will return seconds in 00:00:00 averaged...

ALTER PROCEDURE [dbo].[AHT]
-- Add the parameters for the stored procedure here
@Start DATETIME,
@End DATETIME,
@Agent VARCHAR(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT Agent, /*CallOffered, CallCompletedAfter, CallAnsweredAfter,*/
AVG(CallCompletedAfter - CallAnsweredAfter)AS 'My AHT'
FROM CallsMain
WHERE (CallOffered >= @Start) AND (CallOffered <= @End)
AND (CallCompletedAfter IS NOT NULL) AND(CallCompletedAfter <> '0')
AND (CallAnsweredAfter IS NOT NULL)
AND (CallCompletedAfter <> CallAnsweredAfter)
AND (CallCompletedAfter <> CallAnsweredAfter + 1)
AND (CallCompletedAfter <> CallAnsweredAfter + 2)
AND (CallCompletedAfter <> CallAnsweredAfter + 3)
AND (Agent = @Agent)
GROUP BY Agent
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 12:21:39

Try this:
SELECT CONVERT(CHAR(8), DATEADD(SECOND, 184, 0), 8)

Source from PESO:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=151769

No, you're never too old to Yak'n'Roll if you're too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-19 : 12:33:57

Hi Parsed ok but when pulled into Crystal repors got this error:

This parameter is of DateTime and the correct format is "yyyy-mm--dd:mm:ss". "yyyy" is the four digit year, "mm" is the month......

Any idea?

Thanks

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-19 : 12:44:55

Hi Whats the 184 for, it calculates but 2 minutes out...
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-19 : 14:30:52

The 184 was just an example from the other topic.
It is not easy to help without knowing anything...

Please give DDL, sample data and wanted output.

Best in a manner like this:

No, you're never too old to Yak'n'Roll if you're too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-20 : 03:23:59

Thats a fiar comment.

Does this help.
I need to return the minutes and seconds (average duration) from the total seconds calculated by these 2 fields:
AVG(CallCompletedAfter - CallAnsweredAfter)AS 'Average Duration'

Code thus far which still returns the total seconds...e.g.302

USE [bcs2000]
GO
/****** Object: StoredProcedure [dbo].[AHT] Script Date: 10/20/2010 08:16:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: sd
-- Create date:18 Oct 2010
-- Description: Average Handle Time
-- =============================================
ALTER PROCEDURE [dbo].[AHT]
-- create parameters for input
@Start DATETIME,
@End DATETIME,
@Agent VARCHAR(20)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- return average call duration based on agent selection
SELECT Agent, CONVERT(CHAR(8), DATEADD(SECOND, 184, 0), 8),
AVG(CallCompletedAfter - CallAnsweredAfter)AS 'Average Duration'
FROM Calls
WHERE (CallOffered >= @Start) AND (CallOffered <= @End)
AND (CallCompletedAfter IS NOT NULL) AND(CallCompletedAfter <> '0')
AND (CallAnsweredAfter IS NOT NULL)
AND (CallCompletedAfter <> CallAnsweredAfter)
AND (CallCompletedAfter <> CallAnsweredAfter + 1)
AND (CallCompletedAfter <> CallAnsweredAfter + 2)
AND (CallCompletedAfter <> CallAnsweredAfter + 3)
AND (Agent = @Agent)
GROUP BY Agent
END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 04:01:12

Try this please:
SELECT Agent,
CONVERT(CHAR(8), DATEADD(SECOND, (AVG(CallCompletedAfter - CallAnsweredAfter)), 0), 8) AS 'Average Duration'
--AVG(CallCompletedAfter - CallAnsweredAfter)AS 'Average Duration'
FROM Calls
WHERE (CallOffered >= @Start) AND (CallOffered <= @End)
AND (CallCompletedAfter IS NOT NULL) AND(CallCompletedAfter <> '0')
AND (CallAnsweredAfter IS NOT NULL)
AND (CallCompletedAfter <> CallAnsweredAfter)
AND (CallCompletedAfter <> CallAnsweredAfter + 1)
AND (CallCompletedAfter <> CallAnsweredAfter + 2)
AND (CallCompletedAfter <> CallAnsweredAfter + 3)
AND (Agent = @Agent)
GROUP BY Agent

No, you're never too old to Yak'n'Roll if you're too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-20 : 04:29:23

Hi

It parses ok but getting error in crystal...would getdate function work better?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 04:47:01

Seems to me like you are trying to use the output of that stored procedure as parameter for something in Crystal Reports.
At this point I don't know how to help you.

Maybe another mate here kows what to do or what should be the next question to isolate your problem.

No, you're never too old to Yak'n'Roll if you're too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-20 : 04:53:19

Fred

you've done it mate, it was me not changing the mapping correctly from SQL, gives me the exact result I'm after in the format 00:00:00 and turns seconds to minutes - seconds.

Nice one and cheers for your help...
I thought DATEADD added or subtracted amounts from the datetime field, so dont need a getdate here then, I understand the conversion here but as you have stated I need to make sure the fileds to calculate are after but included in the DATEADD part of the statement, whats the 8 mean.

Any advice appreciated.

Cheers
S

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 05:02:54

The 8 is a styles paramter to the convert() to get the result as hh:mi:ss

See here for possibillities of convert(): http://msdn.microsoft.com/en-us/library/ms187928.aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-20 : 05:11:35

yes that makes sense,
sorry for this but the way my statement is set up it returns me one agent selection, would it be easy to be able to add multiple agents?
do I just:
@agent1
@agent2
@agent3...

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 05:41:09

[code]-- First we need a function to split the given parm array
-- You have to create it only once.
CREATE FUNCTION dbo.fnParseArray (@array VARCHAR(1000),@separator CHAR(1))
RETURNS @T Table (occ int,parmValue varchar(50))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(1000)
declare @occurence int =1
if (left(@array,1)=@separator)
begin
set @array=stuff(@array,1,1,'')
end
if (right(@array,1)<>@separator)
begin
SET @array = @array + @separator
end
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT into @T VALUES (@occurence,@array_value)
set @occurence = @occurence + 1
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END

-- Then you pass the parameter @Agent like this (comma separated): 'Agent1,Agent2,Agent3'

SELECT Agent,
CONVERT(CHAR(8), DATEADD(SECOND, (AVG(CallCompletedAfter - CallAnsweredAfter)), 0), 8) AS 'Average Duration'
FROM Calls
JOIN DBO.fnParseArray(@Agent,',')
ON Agent = parmValue
WHERE (CallOffered >= @Start) AND (CallOffered <= @End)
AND (CallCompletedAfter IS NOT NULL) AND(CallCompletedAfter <> '0')
AND (CallAnsweredAfter IS NOT NULL)
AND (CallCompletedAfter <> CallAnsweredAfter)
AND (CallCompletedAfter <> CallAnsweredAfter + 1)
AND (CallCompletedAfter <> CallAnsweredAfter + 2)
AND (CallCompletedAfter <> CallAnsweredAfter + 3)
--AND (Agent = @Agent)
GROUP BY Agent
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-20 : 06:27:25

Ok, then I can use this function for other SPs when required...
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-20 : 07:11:40

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

sz1
Aged Yak Warrior

555 Posts

Posted - 2010-10-20 : 07:21:59

I've done it the quick way by doing this:
@Start DATETIME,
@End DATETIME,
@Agent VARCHAR(12),
@Agent2 VARCHAR(12),
@Agent3 VARCHAR(12),
@Agent4 VARCHAR(12)

Then passing it like this:
AND Agent IN (@Agent, @Agent2, @Agent3, @Agent4)

But this means you need to know the names and more fields are added to the parameter list, be better with a dropdown or slect from list field.
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources