How to sum of duration between time records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to sum of duration between time records

Author

Topic

Huong
Starting Member

5 Posts

Posted - 2013-08-06 : 06:44:28

Hi all,
In my example, i have a table as below:
Time|Machine
2013-08-05 10:12:00 |M1
2013-08-05 10:15:00 |M1
2013-08-05 10:20:00 |M1
2013-08-05 10:11:00 |M2
2013-08-05 10:15:00 |M2
2013-08-05 10:16:00 |M2
2013-08-05 10:17:00 |M2
2013-08-06 11:12:00 |M1
2013-08-05 11:42:00 |M1
2013-08-05 12:02:00 |M1

I want to have a report for every machine by time that will show how long each machine worked
The result is something like below

M1|8 (minutes)
M2|6
M1|50
Please help me with creating query for that kind of report.
Thanks in advance.

Huong
Starting Member

5 Posts

Posted - 2013-08-06 : 06:48:56

Sorry all,
Follow above , my report should show
2013-08-05|M1|8
2013-08-05|M2|6
2013-08-06|M1|50
Thank all.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-06 : 08:13:25

[code];WITH Cte
AS (SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, Time)) Times, Machine, Time TimePortion
FROM TableName
)
SELECT c1.Times, c2.Machine, MAX(DATEDIFF(MI,c1.TimePortion,c2.TimePortion))
FROM cte c1
JOIN Cte c2 on c1.Times = c2.Times AND c1.Machine = c2.Machine
GROUP BY c1.Times, c2.Machine[/code]

--
Chandu

Huong
Starting Member

5 Posts

Posted - 2013-08-06 : 22:48:42

Thank bandi for your help. It's really nice.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 01:51:30

quote:
Originally posted by Huong

Thank bandi for your help. It's really nice.

Welcome

--
Chandu

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 03:15:04

Here is an easier approach.

DECLARE	@Sample TABLE
	(
		theTime DATETIME NOT NULL,
		theMachine CHAR(2) NOT NULL
	);

INSERT	@Sample
	(
		theTime,
		theMachine
	)
VALUES	('20130805 10:12:00', 'M1'),
	('20130805 10:15:00', 'M1'),
	('20130805 10:20:00', 'M1'),
	('20130805 10:11:00', 'M2'),
	('20130805 10:15:00', 'M2'),
	('20130805 10:16:00', 'M2'),
	('20130805 10:17:00', 'M2'),
	('20130806 11:12:00', 'M1'),
	('20130806 11:42:00', 'M1'),
	('20130806 12:02:00', 'M1');

-- Bandi
WITH Cte 
AS (SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, theTime)) Times, theMachine, theTime AS TimePortion
	FROM @Sample
	)
SELECT c1.Times, c2.theMachine, MAX(DATEDIFF(MI,c1.TimePortion,c2.TimePortion))
FROM cte c1
JOIN Cte c2 on c1.Times = c2.Times AND c1.theMachine = c2.theMachine
GROUP BY c1.Times, c2.theMachine

-- SwePeso
SELECT		DATEADD(DAY, DATEDIFF(DAY, 0, theTime), 0),
		theMachine,
		DATEDIFF(MINUTE, MIN(theTime), MAX(theTime))
FROM		@Sample
GROUP BY	theMachine,
		DATEDIFF(DAY, 0, theTime);

However, both approaches will only work as long there is only one sequence per day.
What about this sample data? What is your expected output from that?

INSERT	@Sample
	(
		theTime,
		theMachine
	)
VALUES	('20130805 10:12:00', 'M1'),
	('20130805 10:15:00', 'M1'),
	('20130805 10:20:00', 'M1'),
	('20130805 10:11:00', 'M2'),
	('20130805 10:15:00', 'M2'),
	('20130805 10:16:00', 'M2'),
	('20130805 10:17:00', 'M2'),
	('20130805 19:12:00', 'M1'),
	('20130805 19:42:00', 'M1'),
	('20130805 20:02:00', 'M1');

I would expect that output to be

20130805   M1   58
20130805   M2   6

Am I correct?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources