[RESOLVED] Booking System - Doing my head in.

[baffled_in_UK]

Hi,

I'm building a booking system for a guy who has a holiday let. I have a Tariff table that looks like this:

tariff_from ....... tariff_to ....... tariff_cost

2009-03-14 ....... 2009-04-11 ....... 175.00
2009-04-11 ....... 2009-04-18 ....... 325.00
2009-04-18 ....... 2009-05-02 ....... 250.00
2009-05-02 ....... 2009-05-09 ....... 325.00
2009-05-09 ....... 2009-05-23 ....... 300.00
2009-05-23 ....... 2009-05-30 ....... 325.00
2009-05-30 ....... 2009-07-11 ....... 275.00
2009-07-11 ....... 2009-09-05 ....... 350.00
2009-09-05 ....... 2009-10-24 ....... 225.00

I'm having the devil of a job working out how to get a set of ranges out of this :
I'm ok if the date range falls within one record but I'm having difficulty if they cross multiple records.

for example; From Date = 2009-04-11 and To Date = 2009-04-25
this should retrieve a total cost of 575.00 (2 records).

I'm sure it must be fairly straightforward but it's not coming to me.
Any help is greatly appreciated.

[Piranha]

I think that the query below will solve it. But please test it to see that I'm right.

select sum(cost)
from table
where from < '2009-04-25'
and to > '2009-04-11'
group by id;

[edit]You might not need the group by, I don't know for sure.[/edit]

[baffled_in_UK]

Spot on. Thank you.