Check for overlapping date range 1

[EriRobert]

Hi

Can anyone come up with a neat where statement to catch overlapping date ranges. My table has a from and to date eg.

ID F_FromDate F_ToDate
1 1-Mar-2008 18-Mar-2008
2 1-Jan-2000 31-Dec-2010
3 10-Mar-2008 01-Apr-2008
4 13-Mar-2008 15-Mar-2008
5 1-Jan-2008 01-Mar-2008
6 25-Mar-2008 09-Sep-2008

My input parameters @datFromDate and @datToDate may be for example

8-Mar-2008 and 23-Mar-2008

in which case they overlap with and return records 1 to 4 and not 5/6.

My clumsy code involves three 'OR's.

Any suggestions?

 

[bborissov]

SELECT * FROM YourTable
WHERE (@datFromDate BETWEEN F_FromDate   AND F_ToDate)   OR
      (@datToDate   BETWEEN F_FromDate   AND F_ToDate)   OR
      (F_FromDate   BETWEEN @datFromDate AND @datToDate) OR
      (F_ToDate     BETWEEN @datFromDate AND @datToDate)

Not tested

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[r937]

where F_ToDate >= @datFromDate
and F_FromDate <= @datToDate



r937.com | rudy.ca

 

[r937]

it helps to use a timeline diagram

      @datFromDate @datToDate 
             |         |               
1   FR---TO  |         |               
             |         |               
2         FR-|-TO      |               
             |         |               
3            | FR---TO |               
             |         |               
4         FR-|---------|-TO       
             |         |               
5            |      FR-|-TO 
             |         |               
6            |         |  FR---TO

where F_ToDate >= @datFromDate eliminates case 1

and F_FromDate <= @datToDate eliminates case 6



r937.com | rudy.ca

 

[EriRobert]

Thanks Rudy - that is a neat solution. I was jumping on the 'Or' route, but the 'And' method is lovely.

Thanks