08-30-10, 08:45 #1Registered User
- Join Date
- Aug 2010
Unanswered: Query/Code to retrieve data for a date range and calculate a total price
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;
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
Phuket Golf Holidays
08-30-10, 18:50 #2Registered User
Provided Answers: 6
- Join Date
- May 2005
- Nevada, USA
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.Paul
08-31-10, 04:52 #3Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
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)
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)
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)
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)
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).
Last edited by izyrider; 08-31-10 at 04:56. Reason: removed dbo. table prefix and fixed typocurrently using SS 2008R2