Query/Code to retrieve data for a date range and calculate a total price

PGH · #1 (**permalink**) 08-30-10, 08:45

Hi,

I'm designing a database for my golf holiday company and want to automate the calculation of a quote price from the Hotel_Price table.

The difficulty I'm having is the price changes for high and low season etc. I'm trying to avoid having to store the hotel price data individually for every day of the year as we have many hotels to cover.

My Hotel_Price table currently stores a single price per season for each room type as follows;

Hotel_Name
Season_Name (High,Low,Peak,Xmas)
Season_Start (Date)
Season_End (Date)
Room_Type
Cost_Single
Cost_Dbl
Sell_Single
Sell_Double

So once the user has input via the quote form a Hotel Name, Room Type and a check in and check out date I want to be able to calculate the total cost price and sell price for the stay.

Am I going to have to create some VBA looping procedure to retrieve a price for each day from check in to check out date and increment the total accordingly ?

Is there an easier way of doing this ?

Thanks in advance
SB
Phuket Golf Holidays

pbaldy · #2 (**permalink**) 08-30-10, 18:50

If my check in/out dates overlap a season, am I charged a single price per day or is the price for each day dependent on the season it falls in? If the cost is based on the start date and I pay the same daily cost for every day of my trip, it would be a simple matter to come up with a cost/day. If as I suspect you want the price for each day dependent on the season, you will need a function to loop through the dates of the stay. I can't think of any pure SQL way to do it.

izyrider · #3 (**permalink**) 08-31-10, 04:52

make yourself a tally table (in my example tblNumbers) containing a single integer field (in my example intNum) indexed unique. folk are unlikely to stay forever, so you might as well populate this table manually with 1,2,3,4,5,6.....to something significantly more than the maximum likely stay in days.

what does that do for you?
try this (replacing @dtIn, @dtOut with your dates - i used 10 ... 20 August)

Code:

SELECT n.intNum, DATEADD(d,n.intNum -1,@dtIn) AS Night
FROM tblNumbers AS n
WHERE n.intNum <= DATEDIFF(d,@dtIn,@dtOut)

Result:
intNum      Night
----------- -----------------------
1           2010-08-10 00:00:00.000
2           2010-08-11 00:00:00.000
3           2010-08-12 00:00:00.000
4           2010-08-13 00:00:00.000
5           2010-08-14 00:00:00.000
6           2010-08-15 00:00:00.000
7           2010-08-16 00:00:00.000
8           2010-08-17 00:00:00.000
9           2010-08-18 00:00:00.000
10          2010-08-19 00:00:00.000

(10 row(s) affected)

where next?
i made a table similar to yours (tblPrice) with fields dtStart, dtEnd, mRate
and populated it with a rate change from 200 to 150 on 16th August.

try this (again replacing @dtIn, @dtOut with your dates)

Code:

SELECT p.mRate
FROM tblPrice AS p
RIGHT JOIN (
			SELECT DATEADD(d,n.intNum -1,@dtIn) AS Night
			FROM tblNumbers AS n
			WHERE n.intNum <= DATEDIFF(d,@dtIn,@dtOut)
		) AS x
ON x.Night >= p.dtStart AND x.Night <= p.dtEnd

Result:

mRate
---------------------
200.00
200.00
200.00
200.00
200.00
200.00
150.00
150.00
150.00
150.00

(10 row(s) affected)

and using SELECT SUM(p.mRate) instead takes you where you want to go.

i don't have Access on this machine to check, but the syntax shouldn't be too far wrong. this tally table route will be **FAR** faster than any loops you can think of.

google 'sqlservercentral tally table' for some background (including ways to auto-populate your tally table).

izy

Internet Services

Web Development

Digital Marketing

Consumer Tech