Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Oct 2008
    Posts
    18
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Return total price for date range

    Hi all,

    I have a booking system which stores different prices for different time periods.

    For instance:

    2011-06-10 - 2011-06-20 = 100
    2011-06-21 - 2011-06-30 = 200

    I have a date range: ( 2011-06-15 - 2011-06-25 ), which is half way between both of the above ranges.

    What is the best way to query the database to get a total price for the supplied date range.

    I was using:

    Code:
    SELECT id,start,end,rate,request FROM `pricebands` 
    WHERE ((start >= '$start' AND start <= '$end') 
    OR (end >= '$start' AND end <= '$end')
    OR (start >= '$start' AND end <= '$end')
    OR (start <= '$start' AND end >= '$end'))
    GROUP BY request ORDER BY request ASC
    but obviously grouping by 'request' (1 or 0) will not return the prices correctly. I thought about looping through each day and returning it's price then calculating but this seems excessive.

    Any ideas?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    The way I'd do it is not a calculation for every day in the range, but a calculation for every price range in the range. So if a range crosses two price ranges, my query will produce two rows.

    In each row you'll need to calculate two things: the number of days in your booking range that falls inside the price range, and the total price for that price range, which is the number of days times price.

    To get number of days you can do something like DATEDIFF(priceEndDate, bookingStartDate).


  •  

    Posting Permissions

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