Computing number of days which fall between predefined date ranges

this is a complex situation I am ain. I have a booking system where rooms are booked.

The thing is that the rates for the rooms aren't stored as a single value but are period based. Like a room can have one daily rate between September to December and adifferent rate from March to August, while have a base rate other wise.

The rates table is like this:

ROOMID | RATE | PERIOD_START | PERIOD_END

Lets suppose that the rate for a room between 1st March to 31st March is 20 Dollars/day and the rate for the same room from 15th April to 30th May is 30 Dollars, aside that the rate is a flat rate of 15 dollars/day.

If this room is booked by one client between 15th March to 10th May, the total cost would be:

15th March - 31st march charged at 20 Dollars/day = 16x20
1st April - 14th April charged at 15 Dollars/day = 14x15
15th April - 10th May charged at 30 Dollars/day = 25x30

Now how can I compute this value in code, I would need to compute thenumber of days based upon the rate periods if any, else use a base rate for them. Its complex but thats how it is. I'm using php MySQL

Solution

this is a possible solution algorithm:

find all rate periods that have a non-empty intersection with the booking period
for each rate period found in (1), compute the number of days in its intersection with the booking period, and multiply by period's rate
the remaining number of days (i.e. length of the booking period in days less sum of all days found in (2) are your base rate days

Re (1), in order to find which rate periods intersect with booking period, note that 2 intervals (A,B) and (C,D) have an empty intersection iff D < A or C > B, so you can just negate this condition. Should look something like:

SELECT * FROM rates 
   WHERE NOT (booking_end < period_start OR 
              booking_start > period_end)

Re (2), you just need to find the number of days between max(booking_start, period_start) and min(booking_end, period_end), inclusive. I am sure there are utilities to deal with dates, but in the worst case you can just loop through it.

Sorry, but I am not an SQL/php wizard to write the actual code... :)