Room Availability SQL Query

[missc]

hi,

i am doing a hotel reservation system and i don't know how to get the rooms available !
can some one help pls ?

i have these tables:

[B][U]tblRoom[/U][/B]
ID--int
RoomNo--int
RoomType--int
BedType--varchar(25)
RatePerNight--money
MaxNoOfPersons--int

[B][U]tblReservation[/U][/B]
ID--int
GuestID--int
DateOfReservation--datetime
NoOfPersons--int
RoomID--int
ArrivalDate--datetime
DepartureDate--datetime
TotalCost--money
CheckIn--bit (-this is set to true on arrival at the hotel)
CheckOut--bit (this is set to true before the departures)
Status--varchar(10) (- this is used to be updated when the reservation is booked, updated or canceled)

[abelLazm]

what is the problem i cant understand ? as you have pretty good database tables you have room id in reservation and its status too so whenever you want to check the available rooms join these two tables on room id and check which of them are reserved and ofcourse the rest will be free

Select r.roomNo, r.ratespernight from tblRoom as r join tblReservations
as res on r.id!=res.roomid or res.status="CANCELED"
// supposed to be tablReservations contain only rooms which are reserved

[missc]

yes the reservations table consits of rooms that are reserved..

the status will be booked on booking, attended on arrival, completed on departure and canceled if cancel.

but the problem is that when i want to make a new reservation, the user has to enter the arrival date and departure date. and i have to show him only rooms available on these dates !! that will be future room availability

[abelLazm]

have you checked the query above?

[missc]

its not working :/ because of the string "canceled"

[abelLazm]

what are the values in status?

[missc]

booked, canceled, updated, completed

[c#Programmer]

i have a similar problem. in my db i have three rooms in tblRooms, i want to check if a new reservation is valid; i need to check if the date that the customer wants to book, is not already booked. the problem is that i need to calculate not only the day of the order, but the duration of the order against all my data in my tblOrder reservation. CAN SOMEONE PLEASE HELP!!!!

[missc]

i cant manage to get the rooms available :/

[shine_jose]

If my view is correct this can be solved with the help of one function and a query
i will just give the base.
consider your first table tblroom with columns(RID(ie your roomid auto increment) bigint,Rate double,roomtype etc)
second table tbleReservation with columns(resid big int primary key identity,rid(foreign key),arrivalDate,departureDate)

The function will check the rid i. e roomid and return null if the room is booked for that date else return null the function will pass three arguments ie rid(roomid),arrivaldate,departuredate

create function GetAvailableroomsfun(@Roomid int,@DATEFROM DATETIME,@DATETO DATETIME)
RETURNS INT
AS
BEGIN
declare @Flage bigint
declare @arrivaldate datetime
declare @departuredate datetime
set @Flage = @Roomid
 
set @arrivaldate = (select arrivaldate from reservation where roomid= @Roomid)
set @departuredate = (select departuredate from reservation where roomid= @Roomid)
if( (convert(varchar,@DATEFROM,10) = convert(varchar,@arrivaldate,10)) or (convert(varchar,@DATETO,10) = convert(varchar,@departuredate,10)))
set @flage = 1
else
begin 

WHILE @DATEFROM < @DATETO
BEGIN

    SELECT @DATEFROM = DATEADD(D, 1, @DATEFROM)
if((convert(varchar,@DATEFROM,10) = convert(varchar,@arrivaldate,10)) or convert(varchar,@DATEFROM,10) = convert(varchar,@departuredate,10))
begin
set @Flage = null
break
end

END
end
return @Flage
End

then use this query

select * from room r inner join reservation res on r.rid = res.roomid  and r.rid in (select
dbo.GetAvailableroomsfun(r.rid,'2011-05-01' ,'2011-05-11') )

Hope it will solve your issue.If its OK please mark this thread as solved

[BitBlt]

Could be simpler. Here's a snippet of SQL code that you can run in your query tool of choice (I'm assuming MSSQL). You'll have to change to appropriate variables to construct your C# database call (I'm not a C# guy, sorry).

declare @arrival as varchar(12)
declare @departure as varchar(12)

select @arrival = '02/01/2011', @departure = '02/06/2011'
--select @arrival = '02/07/2011', @departure = '02/11/2011' -- just more test cases
--select @arrival = '02/09/2011', @departure = '02/11/2011' -- just more test cases

select distinct * 
from dbo.tblRoom a
where not exists
(
select 1 from dbo.tblReservation b1 
where b1.RoomId = a.Id 
and b1.status <> 'Canceled'
and 
    (
    b1.ArrivalDate between @arrival and @departure
    or b1.DepartureDate between @arrival and @departure
    )
)

Please note that it is using single-quote (which might have been your problem from above?) and that declaration of the variables is specific to MSSQL. If you are using some other dialect of SQL, you may have to alter the syntax accordingly.

This query will return all the room information for rooms that are not booked during the specified time period bracketed by @arrival and @departure. If you want, I can post the table defs and test data inserts I used for my testing.

Good luck! I hope this helps.

[c#Programmer]

thanks. i solved my problem

[missc]

thanks for the help.. but i managed to do it using for loop and other conditions !! tnks again