asked on

Detecting Date ranges that overlap

Hi

I have a table that contains startDate and endDate for example

Id StartDate End Date
55 2007-08-01 2007-08-14
56 2007-08-21 2007-08-28
57 2007-09-01 2007-09-12

I want to pass a date range and return the Id of any lines that conflict.
eg
spConflictingDates (2007-08-10, 2007,2007-08-22) would return 55 & 56
spConflictingDates (2007-08-27, 2007,2007-08-30) would return 56

Any help would be greatly recieved

Thanks

Rob

Lukasz Zielinski

create procedure conflictdates
@fromdate
@todate
as
select
Id
from table_name
where (startDate between fromdate and todate ) or (enddate between fromdate and todate)

ziolko.

dportas

Ziolko's solution does not quite capture every case of overlapping date ranges, although it works for the examples given. Try the same query where @fromdate = '20070822' and @todate = '20070827'.

Here's my solution:

SELECT id
FROM table_name
WHERE StartDate <= @todate
AND EndDate >= @fromdate;

Notice that I've assumed an event that ends on @fromdate is still counted as overlapping. If that's not what is required then change the final >= to >.

Lukasz Zielinski

dportas > it depends what he means overlapping dates:) but it's my mistake should ask for more details.

ziolko.

langlro1

ASKER

Thank you both for your comments, I am going to have a play over the next few hours....

langlro1

ASKER

Okay, the problem with dportas solutions is that if I use a wide range of dates no record are returned for example
spConflictingDates (2007-08-01, 2007,2007-09-02) should return 55, 56 & 57

ziolko result does return multiple records which is what I want, however I want to exclude the given toDate and fromDate

For Example

spConflictingDates (2007-08-14, 2007,2007-08-21) would return NO records

Thanks for your help so far

Rob

ASKER CERTIFIED SOLUTION

dportas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

langlro1

ASKER

dportas Thanks... I must of entered the dates the wrong way round or something. I have retested the various scenerios and it works.

thanks again

Rob