|
|
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
Navigation:
[Reply to this message]
|