Find the most Overlapped days?

Question

Im trying to write a query which will emit :

the date/s which Overlapped at most.

the format is d/m/yyyy

so here i have date ranges :

dateStart-----dateEnd

  1/1---7/1                          

         8/1--15/1                   

               16/1------20/1               

         8/1--------------21/1       

                17/1---19/1 

                 18/1--19/1

this is the desired result analyze :

enter image description here

the 2 common days at the left are 8/1 and 9/1 (appears at 2 ranges)

the 4 common days at the right are 18/1 and 19/1 (appears at 4 ranges... and 4>2 so it should win.)

desired result :

18/1

19/1

they both appears the most overlapped.

edit

this is the script of the datetimes ranges.

DECLARE @t table( dt1 DATETIME , dt2 DATETIME)

INSERT INTO @t
SELECT '20110101','20110107'
UNION ALL
SELECT '20110108','20110115'
UNION ALL
SELECT '20110116','20110120'
UNION ALL
SELECT '20110108','20110121'
UNION ALL
SELECT '20110117','20110119'
UNION ALL
SELECT '20110118','20110119'

@TimSchmelter do you mean a script for inserting the shown data ? thanks — Royi Namir, Apr 01 '12 at 11:11
Yes, so that we talk about the same and can compare the result. — Tim Schmelter, Apr 01 '12 at 11:15

score 3 · Accepted Answer · answered Apr 01 '12 at 11:35

3

I'm afraid that this is not exactly what you're looking for, but maybe it helps you anyway(i'm running out of time):

DECLARE @tbl table( startdate DATETIME , enddate DATETIME)

INSERT INTO @tbl
SELECT '20110101','20110107'
UNION ALL
SELECT '20110108','20110115'
UNION ALL
SELECT '20110116','20110120'
UNION ALL
SELECT '20110108','20110121'
UNION ALL
SELECT '20110117','20110119'
UNION ALL
SELECT '20110118','20110119'

;with overlapping_events as(
    select startdate, enddate
       , (select sum(inTimeSpan) 
    from (
       select case when startdate<=events.startdate then 1 else 0 end
         + case when enddate <= events.startdate then -1 else 0 end as inTimeSpan
       from @tbl 
       where startdate <= events.startdate
         or enddate <= events.startdate) as previous
    ) as overlapping
    from @tbl events
)
select oe.* 
from overlapping_events oe 
order by overlapping desc, startdate asc, enddate asc

startdate                     enddate                     overlapping
2011-01-18 00:00:00.000   2011-01-19 00:00:00.000         4
2011-01-17 00:00:00.000   2011-01-19 00:00:00.000         3
2011-01-08 00:00:00.000   2011-01-15 00:00:00.000         2
2011-01-08 00:00:00.000   2011-01-21 00:00:00.000         2
2011-01-16 00:00:00.000   2011-01-20 00:00:00.000         2
2011-01-01 00:00:00.000   2011-01-07 00:00:00.000         1

answered Apr 01 '12 at 11:35

Tim Schmelter

450,073
74
686
939

How the hell did you do that ? wow ! im checking more situations... seems to work. 10 min. – Royi Namir Apr 01 '12 at 11:46
Does `;with` clause run every time it is being called , or there is a performance improvment using CTE ...? – Royi Namir Apr 01 '12 at 11:49
@RoyiNamir: No, actually you don't need it here. Just used it because i thought you need to aggregate the result further. – Tim Schmelter Apr 01 '12 at 11:51
except for the unsed usage , Cte will be run each time it is called ? – Royi Namir Apr 01 '12 at 11:52
@RoyiNamir: This cte is just like a temporary table. I've used it for simplification. Don't mix it with an recursive CTE. It's just a container for the inner query. – Tim Schmelter Apr 01 '12 at 11:56
@TimSchmelter sorry man , can i please forward you to a question of myne ? im pretty stuck here....http://stackoverflow.com/questions/10496979/sql-server-cte-find-top-parentid-foreach-childid – Royi Namir May 08 '12 at 12:53

score 3 · Answer 2 · answered Apr 01 '12 at 15:50

This query will show individual dates with most events. CTE tableOfDates produces a table of dates from min(startDate) to max (enddate). Main part of the query simply counts intervals containing this day. If you want to see complete list, comment out top 1 with ties part. There is Sql Fiddle version of it.

; with tableOfDates as (
   select min (startdate) aDate, max(enddate) enddate
     from tbl
   union all
    select aDate + 1, enddate
      from tableOfDates
     where enddate > aDate
)
select top 1 with ties tableOfDates.aDate, count (*)
from tableOfDates
   inner join tbl
      on tableOfDates.aDate >= tbl.startDate
     and tableOfDates.aDate <= tbl.enddate
group by tableOfDates.aDate
order by 2 desc
option (maxrecursion 0)

score 0 · Answer 3 · answered Apr 01 '12 at 11:55

Great question.

I'd do it by

expanding all date ranges into individual/vertical records.
turn '20110101', '20110107' into
'20110101'
'20110102'
'20110103'
'20110104'
'20110105'
'20110106'
'20110107'
then, grouping by the individual dates and returning the one(s) with max count

Find the most Overlapped days?

edit

3 Answers3