Last post Sep 04, 2016 10:39 PM by Rechousa
Sep 03, 2016 06:48 PM|TechView|LINK
Hello friends! I am trying to sum up time and almost did it. Now I would like to sum up top 2 time as follows:
SELECT TOP 2*,
ISNULL((RIGHT('00' + CONVERT(VARCHAR(10), SUM(DATEDIFF(MINUTE, FromTime, ToTime)) / 60), 2)
+ ':' +
RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(Minute, FromTime, ToTime)) % 60), 2)
+ ':' +
RIGHT('00' + CONVERT(VARCHAR(2), SUM(DATEDIFF(SECOND, FromTime, ToTime)) % 60), 2)), 0)
AS TotalTime FROM Demo GROUP BY ID, FromTime, ToTime
The output is as follows:
So is there any way or still possible to add the sum of the TotalTime column at the end? I would appreciate if any idea is shared. Thanks.
Sep 04, 2016 09:35 PM|mbanavige|LINK
I'd replace your TotalTime column in that query with a TotalSeconds column.
That allows you to use that query as a subquery or cte and then sum up the TotalSeconds across all the rows.
Once you've got your final sum ( Sum(TotalSeconds) as TotalTotalSeconds ), then you'd do the math to turn it into hours/minutes/seconds
also beware that you don't also end up with so many seconds that you need to include a representation for days too.
Sep 04, 2016 10:39 PM|Rechousa|LINK
You can do it with SQL, by using the TIME data type.
-- Declaring a table variable with some values
DECLARE @Times TABLE(id INT, FromTime DATETIME, ToTime DATETIME);
INSERT INTO @Times VALUES (1, '2016-09-03 18:14:47', '2016-09-03 20:32:54');
INSERT INTO @Times VALUES (2, '2016-09-03 12:35:45', '2016-09-03 15:06:06');
WITH CTE(id, FromTime, ToTime, TotalTime)
-- This computes the TotalTime column
SELECT *, CAST((ToTime - FromTime) AS TIME(0)) AS TotalTime FROM @Times
-- This computes the TimeSummary column
SELECT *, (SELECT CAST(DATEADD(SECOND, SUM(DATEDIFF(SECOND,0,CAST(TotalTime AS DATETIME))),0) AS TIME(0)) FROM CTE T WHERE T.id <= CTE.id) AS TimeSummary
It is working, but it was a tiny catch that could be a problem:
Please keep in mind, that since we're using the TIME datatype and it's precision is 24h, you'll have an incorrect result if you're trying to calculate a duration greater that an day.
Hope this helps