# Total days hours and minutes

Hello experts,

I have a table with two columns, the initial date and the final date.
I know how calculate days hours and minutes between them with datediff.

But i need to calculate the total days, hours and minutes of all rows in the end.

inidate                      enddate                      result
01/01/2014 01:30      02/01/2014 03:00      1d 1h 30m
03/01/2014 01:30      05/01/2014 03:00      2d 1h 30m

Total: 2d 3h 00m

Anyone know a easy way to do this?

Miguel
LVL 1
###### Who is Participating?

``````DECLARE @Test TABLE
(
inidate		DATETIME,
enddate		DATETIME
);

INSERT INTO @Test (inidate, enddate)
VALUES ('1/1/2014 01:30', '2/1/2014 09:00'),
('3/1/2014 01:30', '5/1/2014 03:00'),
('2/15/2014 12:34:56', '2/28/2014 01:23:45'),
('3/4/2014 15:45:22', '3/4/2014 16:00:25');

WITH cte
AS
(
SELECT inidate, enddate,
DATEDIFF(DAY, inidate, enddate) AS d,
DATEDIFF(HOUR, inidate, enddate) % 24 AS h,
DATEDIFF(MINUTE, inidate, enddate) % 60 AS m
FROM @Test
WHERE enddate > inidate
)
SELECT inidate, enddate, d, h, m
FROM cte
UNION ALL
SELECT NULL, NULL,
SUM(d) + ((SUM(h) + (SUM(m) / 60)) / 24), ((SUM(h) + (SUM(m) / 60)) % 60) % 24, SUM(m) % 60
FROM cte
``````
0

Senior DeveloperCommented:
E.g.

``````DECLARE @Sample TABLE
(
BeginDateTime DATETIME ,
EndDateTime DATETIME
);

INSERT  INTO @Sample
( BeginDateTime, EndDateTime )
VALUES  ( '01/01/2014 01:30', '02/01/2014 03:00' ),
( '03/01/2014 01:30', '05/01/2014 03:00' );

WITH    Mins
AS ( SELECT   * ,
DATEDIFF(MINUTE, BeginDateTime, EndDatetime) AS Mins ,
SUM(DATEDIFF(MINUTE, BeginDateTime, EndDatetime)) OVER ( ORDER BY BeginDateTime ) AS SumMins
FROM     @Sample
)
SELECT  * ,
Mins / ( 24 * 60 ) AS d ,
( Mins % ( 24 * 60 ) ) / 60 AS h ,
( Mins % ( 24 * 60 ) ) % 60 AS m ,
SumMins / ( 24 * 60 ) AS sd ,
( SumMins % ( 24 * 60 ) ) / 60 AS sh ,
( SumMins % ( 24 * 60 ) ) % 60 AS sm
FROM    Mins;
``````
0

Author Commented:
Hi ste5an,

But that does what i already know, like i said in the post.. you're calculating the total days, hours and mins in each row.

What i need is to have a final sum of all rows.
0

Author Commented:
Thx guys :)
0

Senior DeveloperCommented:
The math for the total is the same as for the row. Thus the running sum sample..
0

Author Commented:
I realized that later.. but in the mean while BriCrowe gave the all answer.
Thx friend.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.