CODEWITHSUNDEEP
sqldatabasems-access
Yo_
Yo_

Reputation: 11

Select between two date

Hi!

I have two tables, "room" and "busy". My busy table contains reservation records containing an arrival date and a departure date.

I wish to make a room selection that is not booked for two dates given.

Someone gives me the date to which he wants to reserve his room and to which he intends to leave, from that I want to be able to show the rooms that are available to him.

Busy Table => |IdRoom||ArrivalDate|DepartureDate|

Room Table => |IdRoom|NumRoom|And so on..|

I tried different queries but I don't get anything.

My tested query:

SELECT Room.*, Busy.* 
FROM Room
INNER JOIN Busy ON Room.RoomdId= Busy.RoomdId
WHERE (@dateArr NOT BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate') 
AND (@dateDep NOT BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate')

Thanks for your help in advance!

Upvotes: 0

Views: 148

Answers (5)

GaryL
GaryL

Reputation: 1460

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.

Upvotes: 0

Yo_
Yo_

Reputation: 11

Ok so i've corrected my query.

That's the query i use:

SELECT * 
FROM Room 
WHERE RoomId in (
             SELECT RoomID FROM Reservation WHERE EndDate <= @startDate OR  
             StartDate >= @endDate
             )

There is surely a better way to do it but it does the job. Thank you all for helping me ;)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

First, you have a problem with the quotes. But I think that is probably just putting the values in the question.

Second, you don't want the columns from busy. There is no match so they are not of interest.

Then the overlap logic is not correct. Something like:

SELECT r.*
FROM Room r LEFT JOIN
     Busy b
     ON r.RoomdId = b.RoomId AND
        @dateArr <= b.DepartureDate AND
        @dateDep >= b.ArrivalDate
WHERE b.RoomId IS NULL;

Your question is unclear whether someone can arrive on a departure date (and vice versa). If so, the <= and >= might need to lose the equality comparisons.

EDIT:

In MS Access, you can use NOT EXISTS:

SELECT r.*
FROM Room r LEFT JOIN
WHERE NOT EXISTS (SELECT 1
                  FROM Busy b
                  WHERE r.RoomdId = b.RoomId AND
                        @dateArr <= b.DepartureDate AND
                        @dateDep >= b.ArrivalDate
                 );

Upvotes: 0

Cogitator
Cogitator

Reputation: 154

It would be helpful to see a few more columns of your tables. And you may want to consider refactoring them - They don't appear to be in 3rd normal form. Also its not clear if the BUSY table contains the entire history of all the times the rooms were rented. This should work if BUSY contains only current reservations:

SELECT IdRoom FROM BUSY WHERE @ArrivalDate >= DepartureDate AND @DepartureDate <= ArrivalDate

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

the query you are trying to use is logically wrong. Inner join has no use here. Please check the query below

SELECT Room.*
FROM Room
WHERE NOT EXISTS 
(
   SELECT 1 FROM Busy WHERE Room.RoomdId= Busy.RoomdId AND
     (@dateArr BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate') AND 
     (@dateDep BETWEEN 'Busy.ArrivalDate' AND 'Busy.DepartureDate')
)

Upvotes: 0

Related Questions