如何在sql查询中获取公共时间间隔 [英] how to get common time interval in sql query

问题描述

Hello Guys请帮帮我这个，（让表名为yash），机器从9-11停止id1，机器从10-12停止id2 ...现在我想要两台机器停止的时间（ 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


....这是我的桌子..现在如何获得公共时间间隔id1从9-11开始，id2从10-12开始，所以我想得到的是那些机器为id1和id2停止的共同时间10-11 ...请尽快回复它的紧急..



注意:::请给我SQL查询为此只提供SQL查询

解决方案

请阅读示例代码中的注释并尝试：

   -    source table  
  DECLARE   @ srcTable  表（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'）
 
   -    CTE用于分割每台机器的小时数 
;  WITH  MyHours  AS  
（
   -   初始值 
  SELECT  MachineId， 1   As  HourNo ，starttime，endTime，startTime  as  startHour，DA TEADD（小时， 1 ，starttime） AS  endHour 
  FROM   @ srcTable  
  WHERE  DATEADD（小时， 1 ，starttime）< = endTime 
   -   递归部分 
  UNION   ALL  
  SELECT  MachineId，HourNo +  1   AS  HourNo，starttime，endTime，endHour  as  startHour，DATEADD（小时， 1 ，endHour） AS  endHour 
  FROM  MyHours 
  WHERE  DATEADD（小时， 1 ，endhour）< = endtime 
）
  INSERT   INTO   @ dstTable （MachineId，HourNo，startHour ，endHour）
  SELECT  MachineId，HourNo，startHour，endHour 
  FROM  MyHours 
  ORDER   BY  MachineId，HourNo 
 
   -   取消评论以下查询以查看结果 
   -    SELECT *  
   -    FROM @dstTable  
   -    ORDER BY MachineId，HourNo  
 
   -   显示所有常见时间 
  SELECT  t1.MachineId  AS  MachineI d1，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 

结果：

 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 

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

解决方案

Please, read comments in the sample code and try this:

--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

这篇关于如何在sql查询中获取公共时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！