CODEWITHSUNDEEP
sql-serversql-server-2008
Royi Namir
Royi Namir

Reputation: 148524

Find the most Overlapped days?

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'

Upvotes: 5

Views: 143

Answers (3)

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

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)

Upvotes: 3

Chris Bednarski
Chris Bednarski

Reputation: 3414

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

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460068

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

Upvotes: 3

Related Questions