create table Rooms
(Room_no char(4) primary key not null,
Room_Status varchar(20) not null,
Room_Type_Code char(4) foreign key references Room_Type(Room_Type_Code))
go
Data in the Table.
1011AvailSTD1
1012AvailSTD1
2011AvailDLX2
Create table Room_Bookings
(Booking_ID char(8) NOT NULL ,
Room_no char(4) NOT NULL Foreign KEY REFERENCES Rooms,
Adult_Num int ,
Child_Num int,
Chck_in_Date date,
Chck_out_Date date,
Rack_Rate decimal(7,2),
Guest_ID char(13)
)
go
Booking_ID R_No Arrv_Date Dep_Date
083044124012 2012-08-15 2012-08-25750.009210021234567
0837441240122012-08-20 2012-09-28750.009210021234567
0855412 10122012-08-15 2012-09-30450.009201112345678
1234567810122012-08-22 2012-08-23450.000987654321123
1234567810122012-08-25 2012-09-23450.000987654321123
The query does nt return correct room_Nos if the Arrv_Date and Dep_date are within the range of booked dates.
e.g Arrv_Date:2012-08-22 ,Dep_date:2012-09-20 ,it will return room 4012 and 1012 ofwhich they are booked by tht date.