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?

Thx in advanced,
Miguel
LVL 1
justaphaseAsked:
Who is Participating?
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
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

Open in new window

0
 
ste5anConnect With a Mentor 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;

Open in new window

0
 
justaphaseAuthor Commented:
Hi ste5an,

Thank you fo your post.
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
justaphaseAuthor Commented:
Thx guys :)
0
 
ste5anSenior DeveloperCommented:
The math for the total is the same as for the row. Thus the running sum sample..
0
 
justaphaseAuthor 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.

All Courses

From novice to tech pro — start learning today.