CODEWITHSUNDEEP
sqlpostgresql
user17297758
user17297758

Reputation:

SQL Check if a date exists between certain 2 dates

I have these tables

How can I list hotels and rooms that were not reserved on "1-Mar-2022"?

What I've tried so far:

SELECT * 
FROM Hotel h, Room r, Booking b
WHERE h.hotelNo = r.hotelNo 
  AND b.roomNo = r.roomNo
  ??? AND NOT (b.dateFrom and b.dateTo not contains ‘2022-03-01’) ???

Upvotes: 1

Views: 88

Answers (3)

jian
jian

Reputation: 4824

Range type available since 9.2! https://www.postgresql.org/docs/9.2/rangetypes.html
Let's refactor using range based on manual tutorial.

CREATE EXTENSION btree_gist;
CREATE TABLE room_booking (
    hottelNO,
    guestNo,
    roomNo text,
    during daterange,
    EXCLUDE USING gist (roomNo WITH =, during WITH &&)
);

then

SELECT * FROM Hotel h,
JOIN  Room r  ON h.HotelNo=r.HotelNo
JOIN room_booking b ON b.roomNo = r.roomNo
 WHERE during @> '2022-03-01' is false;

Upvotes: 0

Stefan
Stefan

Reputation: 145

You can use '2022-03-01' NOT BETWEEN b.dateFrom and b.dateTo

Upvotes: 1

Sergey
Sergey

Reputation: 5217

SELECT * FROM Hotel h,
JOIN  Room r  ON h.HotelNo=r.HotelNo
JOIN Booking b ON b.roomNo = r.roomNo
 WHERE '2022-03-01' NOT BETWEEN b.dateFrom and b.dateTo

Upvotes: 1

Related Questions