Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2010
    Posts
    2

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

    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

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    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

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    Last edited by izyrider; 08-31-10 at 05:56. Reason: removed dbo. table prefix and fixed typo
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •