Return total price for date range

[biggieuk]

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?

 

[fenway]

Sorry -- you mean you need to add the price for each day of a given period, once per day?