Re: Hotel Booking dates subquery

--CELKO-- wrote:

>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));

Thank you , your constraints are far better than my own, table is pretty much the same otherwise.

>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'.

That makes sense, but there is no way of doing that programmatically is there? I'm a bit crap at this.

>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';

Well I ended up with a longer winded version

SELECT room_id,startdate,enddate
FROM booking
WHERE startdate
BETWEEN to_date(booking_start_date,'dd-mm-yy') AND to_date(booking_start_date,'dd-mm-yy') AND enddate
BETWEEN to_date(booking_end_date,'dd-mm-yy') AND to_date(booking_end_date,'dd-mm-yy');

Checks if the rooms start date or end date falls between a booking period... I think.

BTW why do you use the ':' ? Received on Sun Nov 10 2002 - 02:34:25 CET