Select sum then convert seconds to HH:MM:SS

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

General SQL Server Forums

New to SQL Server Programming

Select sum then convert seconds to HH:MM:SS

Author

Topic

sdowney
Starting Member

1 Post

Posted - 2008-08-11 : 11:10:08

I run this select:

SELECT SUM (Assignee_Effort_Seconds) FROM EY7_HPD_TotalTime where Incident_Number = 'INC000000000103' and Form_Name = 'HPD:Help Desk Assignment Log'

and it returns

2466 (this is the total seconds)

Then I want to convert these seconds to HH:MM:SS and I have this select:

SELECT CONVERT(varchar(6), Assignee_Effort_Seconds/3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (Assignee_Effort_Seconds % 3600) / 60), 2)+ ':' + RIGHT('0' + CONVERT(varchar(2), Assignee_Effort_Seconds % 60), 2) from EY7_HPD_TotalTime where Incident_Number = 'INC000000000103' and Form_Name = 'HPD:Help Desk Assignment Log'

My question is how do I marry these two selects into one to produce the result 00:41:06? I apologize ahead of time for any mistakes I'm new to SQL.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-11 : 11:29:27

make a UDF with second select and call the UDF from first select.



CREATE PROCEDURE TotalTime
@Assignee_Effort_Seconds int
RETURNS varchar(10)
AS
DECLARE @RetVal varchar(10)
SELECT @RetVal=CONVERT(varchar(6), @Assignee_Effort_Seconds/3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (@Assignee_Effort_Seconds % 3600) / 60), 2)+ ':' + RIGHT('0' + CONVERT(varchar(2), @Assignee_Effort_Seconds % 60), 2) from EY7_HPD_TotalTime where Incident_Number = 'INC000000000103' and Form_Name = 'HPD:Help Desk Assignment Log'
RETURN @RetVal
GO

SELECT dbo.TotalTime(SUM (Assignee_Effort_Seconds)) FROM EY7_HPD_TotalTime where Incident_Number = 'INC000000000103' and Form_Name = 'HPD:Help Desk Assignment Log'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:42:47

[code]DECLARE @Sample INT

SET @Sample = 234233

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, @Sample % 3600, 0), 108), 1, 2, CAST(@Sample / 3600 AS VARCHAR(12)))[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-11 : 11:45:21

[code]SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, Assignee_Effort_Seconds % 3600, 0), 108), 1, 2, CAST(Assignee_Effort_Seconds / 3600 AS VARCHAR(12)))
FROM EY7_HPD_TotalTime
WHERE Incident_Number = 'INC000000000103'
AND Form_Name = 'HPD:Help Desk Assignment Log'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, SUM(Assignee_Effort_Seconds) % 3600, 0), 108), 1, 2, CAST(SUM(Assignee_Effort_Seconds) / 3600 AS VARCHAR(12)))
FROM EY7_HPD_TotalTime
WHERE Incident_Number = 'INC000000000103'
AND Form_Name = 'HPD:Help Desk Assignment Log'[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources