Calculating Date-wise Total Time in SQL Server

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 789
--Sample Data
DECLARE @tab TABLE( date DATETIME , spendtime TIME)
INSERT INTO @tab
SELECT '2013-01-08 00:00:00.000', '03:20:00' union all
SELECT '2013-01-08 00:00:00.000', '01:19:00' union all
SELECT '2013-01-09 00:00:00.000', '04:33:00'
/*Expected output :

-------------date------------spendtime
2013-01-08 00:00:00.000-----04:39
2013-01-09 00:00:00.000-----04:33
*/

--Method 1
SELECT DATE,
CAST( DATEADD(s, SUM(DATEDIFF(s, 0, spendtime)), 0) AS TIME)
FROM @tab
GROUP BY DATE

-- Method2
;WITH CTE
AS (SELECT [date], SUM(DATEDIFF(s, 0, spendtime)) TotalSecond
FROM @tab
GROUP BY [date]
)
SELECT DATE
, CONVERT(VARCHAR(10),TotalSecond/3600)+ '.' +
CONVERT(VARCHAR(20),TotalSecond%3600/60) + '.' +
CONVERT(VARCHAR(20),TotalSecond%3600%60) AS [TotalTime]
FROM CTE

Comments or Responses

Login to post response