Click here to Skip to main content
15,885,130 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello Guys Please Help me For This,(let table name be yash) ,,machine stopped of id1 from 9-11 ,,machine stopped of id2 from 10-12... now i want time when both machine stops (10-11)

id startdatetime enddatetime

1 1998-6-6 09:00:00 1998-6-6 11:00:00

2 1998-6-6 10:00:00 1998-6-6 12:00:00

....this is my table.. now how to get common time interval id1 has from 9-11 and id2 has from 10-12 so ans i want to get is common time 10-11 between those that machine for id1 and id2 is stopped ...please reply soon its urgent..

Note:::please give me SQL query for that Give only SQL query
Posted
Updated 29-Jul-14 19:08pm
v2
Comments
Yash Raichura 30-Jul-14 0:19am    
can u do for me this urgent??
krishnaMurali 30-Jul-14 0:58am    
what u tried till now?
Sergey Alexandrovich Kryukov 30-Jul-14 1:09am    
Not urgent and not clear.
—SA

1 solution

Please, read comments in the sample code and try this:
SQL
--source table
DECLARE @srcTable TABLE (MachineId INT, startTime DATETIME, endTime DATETIME)
-destination table
DECLARE @dstTable TABLE (MachineId INT, HourNo INT, startHour DATETIME, endHour DATETIME)

INSERT INTO @srcTable (MachineId, startTime, endTime)
VALUES(1, '1998-06-06 09:00:00', '1998-06-06 11:00:00'),
(2, '1998-06-06 10:00:00', '1998-06-06 12:00:00'),
(3, '1998-06-06 11:00:00', '1998-06-06 12:00:00')

--below CTE is used to split hours for each machine
;WITH MyHours AS
(
	--initial value
	SELECT MachineId, 1 As HourNo, starttime, endTime, startTime as startHour, DATEADD(hour,1,starttime) AS endHour
	FROM @srcTable
	WHERE DATEADD(hour,1,starttime)<= endTime 
	--recursive part
	UNION ALL
	SELECT MachineId, HourNo + 1 AS HourNo, starttime, endTime, endHour as startHour, DATEADD(hour,1,endHour) AS endHour
	FROM MyHours
	WHERE DATEADD(hour,1,endhour)<= endtime 
)
INSERT INTO @dstTable (MachineId, HourNo, startHour,endHour )
SELECT MachineId, HourNo, startHour,endHour 
FROM MyHours
ORDER BY MachineId, HourNo 

--uncomment below query to see results
--SELECT *
--FROM @dstTable 
--ORDER BY MachineId, HourNo 

--display all common hours
SELECT t1.MachineId AS MachineId1, t1.HourNo AS HourNo1, t2.MachineId AS MachineId2, t2.HourNo AS HourNo2, t1.startHour, t1.endHour 
FROM @dstTable AS t1 INNER JOIN @dstTable AS t2 ON t1.startHour = t2.startHour AND t1.endHour = t2.endHour AND t1.MachineId < t2.MachineId 
ORDER BY t1.MachineId, t1.HourNo 


Result:
MachineId1	HourNo1	MachineId2	HourNo2	startHour		endHour 
1		2	2		1	1998-06-06 10:00:00.000	1998-06-06 11:00:00.000
2		2	3		1	1998-06-06 11:00:00.000	1998-06-06 12:00:00.000
 
Share this answer
 
Comments
Yash Raichura 4-Aug-14 0:20am    
Thankssssss It Works ...!!!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900