Friday, May 29, 2009

SUM of TIME over 24 HRS

 
DECLARE @TimeCalculation AS TABLE (ID INT IDENTITY, [TIME] TIME)

INSERT INTO @TimeCalculation

SELECT '01:00:00' UNION ALL
SELECT '02:15:00' UNION ALL
SELECT '03:20:00' UNION ALL
SELECT '01:25:00' UNION ALL
SELECT '18:00:00' UNION ALL
SELECT '00:15:00' UNION ALL
SELECT '02:00:00' UNION ALL
SELECT '00:20:00' UNION ALL
SELECT '22:00:00' UNION ALL
SELECT '02:00:00' UNION ALL
SELECT '23:00:00'

SELECT ID, [TIME] FROM @TimeCalculation


SELECT
--Default format that we got to calculate the minutes
CONVERT(CHAR(10),DATEADD(SECOND,ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))), '19000101'), 8) ,

LEFT(

RIGHT(
CONVERT(CHAR(10),DATEADD(SECOND,ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))), '19000101'), 8)
,7)

,2)

AS MIN,
CAST(ABS(SUM(DATEDIFF(SECOND, '00:00:00', [TIME]))) / 3600 AS VARCHAR(12)) AS Hrs
FROM @TimeCalculation

--Now we are using FUNCTION STUFF to get in full format
SELECT

STUFF(CONVERT(CHAR(10), DATEADD(SECOND, ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))), '19000101'), 8), 1, 2, CAST(ABS(SUM(DATEDIFF(SECOND, '00:00', [TIME]))) / 3600 AS VARCHAR(12)))
FROM @TimeCalculation

No comments: