View Full Version : Return total price for date range

05-19-2011, 11:26 AM
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:

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?

05-19-2011, 11:23 PM
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).