Solved

Total days hours and minutes

Posted on 2014-03-27
6
303 Views
Last Modified: 2014-03-27
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
0
Comment
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
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 200 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;

Open in new window

0
 
LVL 1

Author Comment

by:justaphase
ID: 39959084
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
 
LVL 34

Accepted Solution

by:
Brian Crowe earned 300 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

Open in new window

0
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 
LVL 1

Author Closing Comment

by:justaphase
ID: 39959108
Thx guys :)
0
 
LVL 34

Expert Comment

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

Author Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question