Re: Hotel Booking dates subquery

I agree about the vague design of the table. I don't understand the four codes (reserve, check in, check out, cancell); these are actions, not status codes.

CREATE TABLE Bookings
(room_nbr INTEGER NOT NULL

          REFERENCES Rooms(room_nbr),
 party_name VARCHAR(25) NOT NULL DEFAULT '{{vacant}}',  start_date DATETIME NOT NULL,
 finish_date DATETIME, -- null means eternity  CHECK (start_date <= finish_date),
 room_status CHAR(8) DEFAULT 'vacant '

      CHECK (room_status IN ('vacant ', 'occupied'))  PRIMARY KEY (room_nbr, start_date));  

A room is reserved if the start date is in the future and the status is occupied. A room is cancelled when a future date is set to 'vacant'.

For vacant rooms on a particular date use:

 SELECT :my_date, room_nbr
   FROM Bookings AS B1
  WHERE :my_date

        BETWEEN B1.start_date 
            AND COALESCE(B1.finish_date, CURRENT_TIMESTAMP)