Overlapping Dates

[timmi30]

Hi Everyone!

I am desperatly trying to write a query / view that will show available holiday dates, however i am a bit stuck and was wondering if someone kind would help me out.

Table Design:

id (autonumber)
start_date (datetime)
end_date (datetime)
available (yes / no)

Example Data:

#1, 05/03/2005, 12/03/2005, yes
#2, 12/03/2005, 18/03/2005, yes
#3, 15/03/2005, 17/03/2005, no
#4, 11/03/2004, 13/03/2005, yes

The output that i am trying to achieve from the query would be that records number 1,3 and 4 are returned. As record 3 cancels makes record 2 unavailable because it is within it's date range.

I hope this makes sense, any suggestions would be much appreciated as this is driving me nuts!! Thanks

 

[Ukraine82]

Correct me if I'm wrong, but are you trying to retrieve all records that are yes in available field?

If so, set your criteria to "yes" on available field,

Michael

 

[The_Doc_Man]

When looking at date overlaps, the following four expressions are important to detect said overlaps.

Assume for this discussion that S1 and E1 are the start and end of event 1 and that S2 and E2 are the start and end of event 2. You also have to decide about times. Assuming that in your case, the Start Time is always the beginning of the day, say 08:00, and the End time is always the end of the day, say 17:00, and remember that for Date/Time variables, the > means "later" and the < means "earlier"

Two events #1 and #2 (with times as noted above) overlap when

1. S1 < S2 and E1 > S2 (covers #1 overlapping at front of #2 or containing #2 in its entirety)

2. S1 < E2 and E1 > E2 (covers #1 overlapping at rear of #2 )

3. S2 < S1 and E2 > S1 (covers #2 overlapping at front of #1 or containing #1 in its entirety)

4. S2 < E1 and E2 > E1 (covers #2 overlapping at rear of #1)

If you leave out the times, the rules have to change, maybe, if the end time is assumed to include the entire last day of the range.

1. S1 <= S2 and E1 >= S2
2. S1 <= E2 and E1 >= E2
3. S2 <= S1 and E2 >= S1
4. S2 <= E1 and E2 >= E1

 

[timmi30]

No, I am trying return dates availiable dates that aren't being overlapped by unavailable dates.

An avaiable date has a yes in the 4th column, unavailable has no.

So in the example records 2 and 3 the dates overlap one another, in this instance i do not want the record returned when this occurs - hope this explains it.

 

[timmi30]

Thanks Doc Man,

Times are not relevant assume entire day is used! Does this make a difference. Based on your suggestion what would be the SQL construct?

Thanks in advance!