There are 4 scenarios where the date comparisons should say the room is busy. @Simonare was on the right track by using a sub-selection for the busy rooms, and then excluding them from the main SELECT * FROM Room
My kludgy little table illustrates the 4 failure scenarios:
12345678 -> Eight Dates in a row.
..BBBB.. -> Assume: These four Dates are 'B' busy. 'D' dates below are desired/requested.
.D...... -> Success: Desired is before busy dates
......D. -> Success: Desired is after busy dates
..DD.... -> Fails: Desired within busy dates
.DD..... -> Fails: Desired overlaps starting before beginning of busy dates
.....DD. -> Fails: Desired overlaps extending after ending of busy dates
.DDDDDD. -> Fails: Desired extends both before and after busy dates
These are the WHERE clauses to remove the failures:
Request within busy dates: The Busy arrival date AT or AFTER desired arrival date AND Busy departure date LE desired departure date
SELECT 'Busy.DateArr' <= @dateArr AND 'Busy.DateDep' >= @dateDep
Request starts before Busy beginning: The desired arrival date is BEFORE the Busy arrival date AND the desired departure date is AFTER the Busy arrival date)
SELECT @dateArr < 'Busy.DateArr' AND @dateDep > 'Busy.DateArr'
Request ends After Busy ending: The desired arrival date is BEFORE Busy departure date AND the desired departure date is AFTER the Busy departure date
SELECT @dateArr < 'Busy.DateDep' AND @dateDep > 'Busy.DateDep'
Request extending beyond both begin & end: The desired arrival date is BEFORE Busy arrival date AND desired departure date is AFTER Busy departure date
SELECT @dateArr < 'Busy.DateArr' AND @dateDep > 'Busy.DateDep'
So the entire SQL becomes...
SELECT * FROM Room
WHERE NOT EXISTS (
SELECT 1 FROM Busy WHERE Room.RoomdId= Busy.RoomdId AND
((@dateArr>= 'Busy.DateArr' AND @dateDep<= 'Busy.DateDep') OR
(@dateArr < 'Busy.DateArr' AND @dateDep > 'Busy.DateArr') OR
(@dateArr < 'Busy.DateDep' AND @dateDep > 'Busy.DateDep') OR
(@dateArr < 'Busy.DateArr' AND @dateDep > 'Busy.DateDep') )
)
Apologies, I haven't actually run this, so with the complexity, there may be a logic or syntax error. You can test and debug each SELECT portion individually to ensure they are correct. Also, the judicious use of a little Boolean Algebra could significantly simplify the statement.
And ... I am assuming someone can arrive on a departure date.
Busy.ArrivalDateandBusy.DepartureDate. - Joel Coehoorn