Date range logic

[bxgirl]

I attempting to returing records that fall within a given date range and any overlapping dates

For example:
input parameters are:
startDate= 2005-10-31
endDate = 2005-11-30

If I have the following data in my table:

cust dateBeg dateEnd
------------------------
1 2005-10-27 2005-11-15
2 2005-11-1 2005-11-30
3 2005-11-20 2005-12-20
4 2005-12-1 2005-12-10

I want to retrieve cust 1,2,3, but not 4
So far this logic retrieve all dates that fall in between

(dateBeg >= startDate and
dateEnd <= endDate)

How can I include ovelapping dates?

 

[towerbase]

If I understand your requirements correctly then either the start date or the end date must lie within the desired range. This can be expressed as:

( ( dateBeg >= startDate ) AND ( dateBeg <= endDate ) )
OR
( ( dateEnd >= startDate ) AND ( dateEnd <= endDate ) )

Andrew
Hampshire, UK

 

[r937]

let's call your input parameters SD and ED:
SD = 2005-10-31
ED = 2005-11-30

let's call your table columns DB and DE
cust DB DE
1 2005-10-27 2005-11-15
2 2005-11-01 2005-11-30
3 2005-11-20 2005-12-20
4 2005-12-01 2005-12-10

here are all the possibilities:

            SD        ED             
             |         |               
1   DB---DE  |         |               
             |         |               
2         DB-|-DE      |               
             |         |               
3            | DB---DE |               
             |         |               
4         DB-|---------|-DE       
             |         |               
5            |      DB-|-DE 
             |         |               
6            |         |  DB---DE

the matches you are looking for are cases 2 through 5

only cases 1 and 6 should not be returned

there are many ways to set up the WHERE clause, but here's the easiest --

... where ED >= DB /* eliminates case 6 */
and SD <= DE /* eliminates case 1 */


r937.com | rudy.ca

 

[bxgirl]

Many thanks to the both!!

towerbase - I ended up with your logic towards the end.

r937 - I like your diagram...very clear and the solution so simple.