How To find overlapping Dates in SQL Server

Question

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

Are you actually just asking for `The total number of hours that ANY server was down?` So, if ten servers are down for an hour, that's still just one hour of down time? And if just one server is down for a different hour, that's still another one hour of down time? — MatBailie, Aug 20 '13 at 13:47

Gordon Linoff · Answer 1 · 2013-08-19T15:12:01.063

4

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

edited Aug 19 '13 at 15:12

answered Aug 19 '13 at 14:19

Gordon Linoff

1,242,037
58
646
786

But the above amswer wont work for below scenario create table #period ( Sitecode varchar(20), svrname varchar(10), StartTime datetime, downtimeEnd datetime ) go insert into #period values ('A','S1','2013-10-10 07:00:00' , '2013-10-10 07:10:00'), ('B','S2','2013-10-10 08:10:00' , '2013-10-10 08:20:00'), ('C','S3','2013-10-10 08:10:00' , '2013-10-10 08:30:00'), ('A','S1','2013-10-10 08:15:00' , '2013-10-10 08:25:00') select * from #period – anjali.deshetti Aug 19 '13 at 14:27
@anjali.deshetti . . . First, when you have a desire to put code in a comment, think first of editing your question. It makes it much more readable. Second, the data in your comment has no overlaps. And the above query will not return anything. – Gordon Linoff Aug 19 '13 at 14:28
1

@anjali.deshetti If you have additional requirements and new sample data, please update your question. – Nenad Zivkovic Aug 19 '13 at 14:30
@GordonLinoff My wild guess here is that OP wants some overlap between distinct `SiteCode` values. (Periods when all sites were down) – Nenad Zivkovic Aug 19 '13 at 14:36
Hi i apologies, i have edited question hope this time it is clear. – anjali.deshetti Aug 19 '13 at 15:10
@GordonLinoff How will i get to know weather the date is overlapping or not as i want exact hours which mentioned in example – anjali.deshetti Aug 19 '13 at 15:22

Nenad Zivkovic · Answer 2 · 2013-08-19T14:37:48.190

4

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

edited Aug 19 '13 at 14:37

answered Aug 19 '13 at 14:27

Nenad Zivkovic

18,221
6
42
55

score 0 · Answer 3 · edited Aug 20 '13 at 13:39

0

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.

edited Aug 20 '13 at 13:39

Rasim

1,276
1
11
24

answered Aug 20 '13 at 13:17

user2700035

1

How To find overlapping Dates in SQL Server

3 Answers3

Linked