如何在Server中查找重叠日期

Question

我有一个有开始时间和结束时间的表。如何找到其他日期中重叠的特定日期时间:参见下面的示例

create table #period (
    id int,
    starttime datetime,
    endtime datetime
  );

insert into #period values 
(1,'2013-10-10 08:00:00' , '2013-10-10 10:00:00'), 
(2,'2013-10-10 08:10:00' , '2013-10-10 08:20:00'), 
(3,'2013-10-10 08:10:00' , '2013-10-10 08:30:00') 
(4,'2013-10-10 08:15:00' , '2013-10-10 08:25:00') 

select * from #period


required output is '2013-10-10 08:15:00' , '2013-10-10 08:20:00' is getting overlapped in all the dates.

expected output: '2013-10-10 08:15:00' '2013-10-10 08:20:00' 5 Min

编辑：

我对前面的问题表示歉意，这里是适当的细节和例子。

create table #period 
(
    Sitecode varchar(20),
    svrname varchar(10),
    StartTime datetime,
    downtimeEnd datetime
) 
go  

insert into #period values 
('A','S1','2013-10-10 10:00:00' , '2013-10-10 11:00:00'), 
('A','S2','2013-10-10 10:00:00' , '2013-10-10 11:00:00'),
('A','S3','2013-10-10 10:00:00' , '2013-10-10 11:00:00'), 
('A','S1','2013-10-10 03:00:00' , '2013-10-10 03:30:00'), 
('A','S2','2013-10-10 06:30:00' , '2013-10-10 07:30:00')

select * from #period

预期产出如下：

“A”共2.5小时(详见下文)“2013-10-10:00”、“2013-10-10 11:00”“重叠”-2013-10 03:00:00、“2013-10-10 03:30:00”、“2013-10-10 3:30:00”、“2013-10-10 0 6:30:00”、“2013年-10-10 07:30:00”、“2013-10-10 03:30:00”、“2013年-10-10 07:30”-1小时

输出按sitecode分组。重叠日期只考虑过一次。需要最后的产出：

Sitecode 'A‘= 2.5小时

Answer 1

如果您有一组周期，并希望所有这些周期的重叠，那么重叠将是在最大开始时间和最小结束时间之间。

select cast(starttime as date) as thedate, max(starttime), min(endtime)
from #periods
group by cast(starttime as date);

这假设开始和结束日期相同。第一个只是从日期时间值中提取日期(这可能因数据库而异)。这里的版本是用于Server的。

当min(endtime)小于max(starttime)时，没有重叠。因此，要获得有重叠的日期：

select cast(starttime as date) as thedate, max(starttime), min(endtime)
from #periods
group by cast(starttime as date)
having min(endtime) > max(starttime);

编辑：

如果希望在站点代码中重叠，只需将其添加到“组”中：

select sitecode, cast(starttime as date) as thedate, max(starttime), min(endtime)
from #periods
group by sitecode, cast(starttime as date)
having min(endtime) > max(starttime);

Answer 2

这是在没有HAVING的情况下使用GROUP BY的少数例子之一(是的，这是可能的)：

SELECT MAX(starttime), MIN(endtime)
FROM #period
HAVING MIN(endtime) > MAX(starttime);

SQLFiddle演示

Answer 3

请试试这个：

SELECT SUM(b.tm) FROM (
  SELECT datediff(second, StartTime, downtimeEnd) AS tm FROM ( 
    SELECT DISTINCT StartTime, downtimeEnd FROM #period 
  ) AS a
) AS b

输出为9000秒。比你能把秒转换成天，小时，分钟和秒。

希望这能帮到你。