Re: Hotel Booking dates subquery

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 9 Nov 2002 16:28:53 -0800
Message-ID: <c0d87ec0.0211091628.2c8f3dda@posting.google.com>

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)

    AND B1.room_status = 'vacant'; Received on Sat Nov 09 2002 - 18:28:53 CST