Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Total days hours and minutes

Posted on 2014-03-27
Medium Priority
309 Views
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
0
Question by:justaphase
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 35

Assisted Solution

ste5an earned 800 total points
ID: 39958851
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

LVL 1

Author Comment

ID: 39959084
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

LVL 34

Accepted Solution

Brian Crowe earned 1200 total points
ID: 39959099
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

LVL 1

Author Closing Comment

ID: 39959108
Thx guys :)
0

LVL 35

Expert Comment

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

LVL 1

Author Comment

ID: 39959212
I realized that later.. but in the mean while BriCrowe gave the all answer.
Thx friend.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. â€¦
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month7 days, 1 hour left to enroll