Prices on Date Schedule

[Guest]

I have a query based on two tables - tblRooms and tblRoomPrices. The tables
are connected in a one (tblRoom Prices) to many (tblRooms) relationship using
the txtRoomType field. This field is the primary key and contains values of
"Single", "Double", "Twin" & "Family", the other field (curRoomPrice)
contains the respective costs of the rooms.
The user selects the Room Type (tblRoom) and the room price is displayed in
the Room Price field.

This query is used when adding a booking to a hotel booking database in
conjuction with the bookings table (contains arrival, departure dates etc). I
would like to enhance the flexibility by making a room price schedule where
the room prices would be dependant upon the arrival date. I would add a start
and end date to the room price table which would allow the user to set up a
price schedule based on selected dates.

The problem I'm struggling with is how to look up the room price dependant
upon the value in the arrival date field.

Any suggestions would be greatly appreciated;

Thanks ... Dave

 

[Marshall Barton]

I have a query based on two tables - tblRooms and tblRoomPrices. The tables
are connected in a one (tblRoom Prices) to many (tblRooms) relationship using
the txtRoomType field. This field is the primary key and contains values of
"Single", "Double", "Twin" & "Family", the other field (curRoomPrice)
contains the respective costs of the rooms.
The user selects the Room Type (tblRoom) and the room price is displayed in
the Room Price field.

This query is used when adding a booking to a hotel booking database in
conjuction with the bookings table (contains arrival, departure dates etc). I
would like to enhance the flexibility by making a room price schedule where
the room prices would be dependant upon the arrival date. I would add a start
and end date to the room price table which would allow the user to set up a
price schedule based on selected dates.

The problem I'm struggling with is how to look up the room price dependant
upon the value in the arrival date field.

With both a start and end date in the price table, you can
use something like:

SELECT R.roomid, R.roomtype, R.bookdate, . . .,
P.Price
FROM tblRooms As R INNER JOIN tblRoomPrices As P
ON P.txtRoomType = R.roomtype
AND P.startdate <= R.bookdate
AND P.enddate >= R.bookdate
WHERE . . .


Having both the start and end dates for when a price is
effective just has to violate some rule of normalization
though. If you would prefer, you can use just the start
date by assuming that the next start date is also this
record's end date. In this case, you can use a subquery
instead of the above Join query (which is probably faster).

The general idea would be something like:

SELECT R.roomid, R.roomtype, R.bookdate, . . .,
(SELECT TOP 1 P.Price
FROM tblRoomPrices As P
WHERE P.txtRoomType = R.roomtype
AND P.pricedate <= R.bookdate
ORDER BY P.pricedate DESC) As Price
FROM tblRooms As R
WHERE . . .