How To find overlapping Dates in SQL Server

问题:

I have a table which has startdatetime and enddatetime. how to find the the particuler datetime which is overlapping in other dates: see below example

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

EDIT:

I apologies for previous question Here it is the proper details with example

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

Expected Output is:

Sitecode 'A' 
       Total 2.5 hours (below is the details)
       '2013-10-10 10:00:00' , '2013-10-10 11:00:00' getting overlapp - 1 hour
       '2013-10-10 03:00:00' , '2013-10-10 03:30:00' - .5 hour
       '2013-10-10 06:30:00' , '2013-10-10 07:30:00'- 1 hour

The output is group by sitecode. and the overlapping date considered only once. The final output is required:

Sitecode 'A' = 2.5 hours

回答1:

This is one of few examples where it could make sense to use HAVING without GROUP BY (yes it's possible):

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

SQLFiddle DEMO

回答2:

If you have a set of periods and want the overlaps of all of them, then the overlaps are going to be between the maximum starttime and the minimum end time.

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

This assumes that the start and end are on the same date. The first just extracts the date from the datetime values (and this may vary depending on the database). The version here is for SQL Server.

You have no overlaps when min(endtime) is less than max(starttime). So, to get dates with an overlap:

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

EDIT:

If you want overlaps within a site code, just add that into the `group by:

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

回答3:

Please, try this:

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

The output will be 9000 Seconds. Than you can convert Seconds into Days, Hours, minutes and seconds.

Hope this helps you.