Posted by --CELKO-- on 11/16/05 15:58

CREATE TABLE Rooms
(room_nbr INTEGER NOT NULL PRIMARY KEY,
bed_size CHAR(1) NOT NULL
CHECK (bed_size IN ('K', 'Q', 'T', 'D')),
..);

CREATE TABLE Bookings
(room_nbr INTEGER NOT NULL
REFERENCES Rooms(room_nbr)
start_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
end_date DATETIME, -- null mean occupied,
..);

>> return me ALL the rooms which are available. <<

SELECT room_nbr
FROM Rooms
WHERE room_nbr
NOT IN (SELECT rom_nbr
FROM Bookings
WHERE @my_date BETWEEN start_date
AND COALESCE (end_date, CURRENT_TIMESTAMP);

Now create a calendar table for other things and get a copy of DSQL FOR
SMARTIES which has a whole section on this knd of query

[Back to original message]