IWebBiz Dot Net Development

I am designing a booking engine for hotel rooms and to check for availabilities you have to select a range of dates to check if they are available and if not dont return a result for that room. This seems like a simple SQL statement but it is not as easy as it seems. I got around the problem by selecting a range of dates e.g.

SELECT Rooms.id,Rooms.Name,Rooms.Description,Allotments.NumberAvailable,ADate,Rates.RoomsRate FROM Rooms,Allotments,Rates,Seasons WHERE Seasons.RoomsID=Rooms.id AND Seasons.SDate=ADate AND Seasons.id=Rates.SeasonsID AND Rooms.id=Allotments.RoomsID AND (ADate='2008-09-12' OR ADate='2008-10-13') AND COUNT(ADate)=2 GROUP BY Rooms.id ORDER BY Rooms.id,ADate

Then grouping by the room id and counting the number of results. This way the OR becomes an AND except it returns a result for each date and it only returns a result if all the dates are found. This can be used in seperate groups of dates e.g. 1st->3rd and 5th->6th as long as each individual variable is counted. You could even make sure that the “NumberAvailable” is equal to the number of rooms required. To even simplify an allotments query for hotels you just take out the returning date field and SUM the room rate and extra guests rate (ExtraPAX) and you have a complete result.

SELECT Rooms.id,Rooms.Name,Rooms.Description,SUM(Rates.RoomsRate)+SUM(Rates.ExtraPAX*(?NumberOfGuests-Rates.IncludedGuests)) AS TotalCost
FROM Rooms,Allotments,Rates,Seasons
WHERE Seasons.RoomsID=Rooms.id AND Seasons.SDate=ADate
AND Seasons.id=Rates.SeasonsID AND Rates.RoomsID=Rooms.id
AND Rooms.id=Allotments.RoomsID AND (ADate='2008-09-12' OR ADate='2008-10-13')
AND COUNT(ADate)=2 AND Allotments.NumberAvailable>=?RoomsRequired
AND Rooms.MaximumGuests>=?NumberOfGuests
GROUP BY Rooms.id
ORDER BY TotalCost

If you want to make it a multi-property booking engine just add the property table to the SQL query as follows:-

SELECT Properties.id,Properties.Name,Rooms.id,Rooms.Name,Rooms.Description,SUM(Rates.RoomsRate)+SUM(Rates.ExtraPAX*(?NumberOfGuests-Rates.IncludedGuests)) AS TotalCost
FROM Properties,Rooms,Allotments,Rates,Seasons
WHERE Properties.id=Rooms.PropertiesID
AND Seasons.RoomsID=Rooms.id AND Seasons.SDate=Allotments.ADate
AND Seasons.id=Rates.SeasonsID AND Rates.RoomsID=Rooms.id
AND Rooms.id=Allotments.RoomsID
AND (ADate='2008-09-12' OR ADate='2008-10-13') AND COUNT(ADate)=2
AND Allotments.NumberAvailable>=?RoomsRequired
AND Rooms.MaximumGuests>=?NumberOfGuests
GROUP BY Rooms.id
ORDER BY Properties.id,TotalCost

This technique could be used in any sort of situation where a variable in a number of rows needs to be in a set of predefined values and to return a result row for every one of the values required or return none of them. Also as shown above the rows can be grouped by a common factor and functions can be run on columns within the rows selected and totals for the sets of rows can be returned as a single row.

Hope this helps.
Dan.

Trackback this Post | Feed on comments to this Post