How many records overlap on a date range

Question

Post reply

How many records overlap on a date range

Luke79

SSCrazy

Points: 2571
More actions
July 10, 2007 at 2:13 pm

#196848

2007-07-10 07:56:24.243 2007-07-10 07:56:25.743
2007-07-10 07:56:30.070 2007-07-10 07:56:30.447
2007-07-10 07:56:30.773 2007-07-10 07:56:56.620
2007-07-10 07:56:36.230 2007-07-10 07:56:36.230
2007-07-10 07:56:48.883 2007-07-10 07:56:48.883
2007-07-10 07:56:49.197 2007-07-10 07:56:50.510
2007-07-10 07:56:52.010 2007-07-10 07:56:52.917
2007-07-10 07:56:56.197 2007-07-10 07:56:57.480
How to find out how many rows overlap in a date range?
Take rows above as an example. There are 8 rows, but 7 overlap. I was wondering how this can be quickly extracted through SQL.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Christopher Klein SSCrazy Points: 2648 More actions · Answer 1

declare

@table table

(

intID int IDENTITY(1,1)

,dtDATESTART DATETIME

,dtDATEEND DATETIME

)

insert @table

select '01/01/2001','01/31/2001'

union

select '01/01/2001','01/15/2001'

union

select '02/01/2001','02/28/2001'

union

select '01/31/2001','04/01/2001'

union

select '06/01/2007','06/30/2007'

--select t1.intID,t2.dtDATESTART,t2.dtDATEEND,t2.intId

select count(t1.intID) as [Overlaps]

from @table t1

INNER JOIN @table t2

on t1.dtDATESTART BETWEEN t2.dtDATESTART and t2.dtDATEEND

and t1.intID<>t2.intId

shows the number of overlaps, 4 out of 5 records. if you uncomment the commented line it will show you where the records overlap. Might not be exactly what you are looking for but derived tables are your friend

--Chris

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Chris, you need more conditions in your join to get all possible overlaps.

on
 ( t1.dtDATESTART BETWEEN t2.dtDATESTART and t2.dtDATEEND )  or
 ( t1.dtDATEEND BETWEEN t2.dtDATESTART and t2.dtDATEEND )  or
 ( t1.dtDATESTART < t2.dtDATESTART and t1.dtDATEEND > t2.dtDATEEND )

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

Not sure you need that many conditions even to accomplish the 3rd condition... this should do the trick...

select count(t1.intID) as [Overlaps]

from @table t1

INNER JOIN @table t2

on t1.dtDATESTART <= t2.dtDATEEND

AND t1.dtDateEnd >= t2.dtStartDate

and t1.intID<>t2.intId

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)