Room availability

[Steven Blair]

My problem is trying to calcuate whether a room is booked during a date
period.

I have a table with two columns (Start and End date).

I need some SQL code to calculate whether a room is booked during a date
range.

e.g.

The booking entry is:

Start
21/11/2005

End
25/11/2005

Any help on this would be appreciated.

The End Date indicates the last night of the stay.

Regards,

Steven

*** Sent via Developersdex http://www.developersdex.com ***

[SQLChallenge]

This is a good one. Please poke some holes in what I came up with.

How about this though?? Let me know how you make out. The two dates I
am "plugging" would be the beginning and ending date of the period.

create table room (
RoomNumber varchar(5),
StartDate datetime,
EndDate datetime
)

insert into room values('123','2005-10-04','2005-10-05')
insert into room values('123','2005-10-06','2005-10-07')
insert into room values('123','2005-10-09','2005-10-10')

select distinct roomnumber
from room r1
where startdate not between '2005-10-08' and '2005-10-09'
and enddate not between '2005-10-08' and '2005-10-09'
and not exists(select * from room r2
where r2.roomnumber = r1.roomnumber
and startdate between '2005-10-08' and
'2005-10-09'
and enddate between '2005-10-08' and '2005-10-09')

---
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
---

[Steven Blair]

This problem has been driving me crazy lol
I run some tests on your code last night and it seems to work.

I am gonna give it a bit more testing tomorrow and adapt the code to
work with multiple rooms, so I will keep you posted.

Thanks for this help :)

[activecrypt]

Hi,
what if you add a new column
alter table room
add RoomAvailable char(1)
where value is either 'Y' = room available
or 'N' = room not available

Regards

ActiveCrypt
----------------------------------------------
http://www.activecrypt.com

[Steven Blair]

Hi,

Still having a problem.

I need to use two tables, one for Rooms and one for the actual Booking.
How could I modify this code to work with the two tables AND return me
ALL the rooms which are available.

At the moment, I can only return any rooms which have been previously
booked.

[--CELKO--]

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