Partial intervals

Hi everybody,
we have these 2 tables: 'resources' with just an id as key (we don't mind other columns now) and 'periods' with a key, a foreign key to a resource, a start time, an end time (both timestamps) and a price (integer or not, it doesn't matter).
These periods express the resources fares; suppose a bungalow: from 12th Dec 2004 to 23rd Dec 2004 $150 per night but from 24th Dec to 27th Dec $250 and so forth....
Given a check-in date and a check-out one, we'd like to calculate the total fare for this bungalow, even for mixed-period staying (from 20th to 25th December, in our example).
I thought something like SUM(price)s grouped by periods.id could work... but I don't feel comfortable with check-in/out dates that fall in the middle of a period... Has anyone an idea about it?
Thanks in advance, bye!

okay, your results will depend on the checkin and checkout dates, so those are two variables

first, start with an integers table, which has just one column called i, which takes values 0, 1, 2, 3, ...

now select rows from this table based on checkin and checkout dates, like this --

SELECT DATE_ADD($checkin, INTERVAL i DAY)
FROM integers
WHERE DATE_ADD($checkin, INTERVAL i DAY) <= $checkout

(the above uses mysql syntax, your mileage may vary)

to get that working, just run it a few times to make sure it generates the correct range of dates

now join the above to your periods table, such that the date generated lies between the startdate and enddate of the period

to get that working, add the period columns to the SELECT list, and run it a few times to make sure it joins each generated date to the correct range of period startdates and enddates

now remove everything from the SELECT list except the resource foreign key, add SUM(price), and add a GROUP BY on the resource foreign key

voila