Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
05-19-2011, 11:26 AM #1
- Join Date
- Oct 2008
- Thanked 0 Times in 0 Posts
Return total price for date range
I have a booking system which stores different prices for different time periods.
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:
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
05-19-2011, 11:23 PM #2
- Join Date
- Dec 2005
- Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
- 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