php - Computing number of days which fall between predefined date ranges -

this complex situation ain. have booking system rooms booked.

the thing rates rooms aren't stored single value period based. room can have 1 daily rate between september december , adifferent rate march august, while have base rate other wise.

the rates table this:

roomid | rate | period_start | period_end 

lets suppose rate room between 1st march 31st march 20 dollars/day , rate same room 15th april 30th may 30 dollars, aside rate flat rate of 15 dollars/day.

if room booked 1 client between 15th march 10th may, total cost be:

15th march - 31st march charged @ 20 dollars/day = 16x20 1st april - 14th april charged @ 15 dollars/day = 14x15 15th april - 10th may charged @ 30 dollars/day = 25x30 

now how can compute value in code, need compute thenumber of days based upon rate periods if any, else use base rate them. complex thats how is. i'm using php mysql

this possible solution algorithm:

1. find rate periods have non-empty intersection booking period
2. for each rate period found in (1), compute number of days in intersection booking period, , multiply period's rate
3. the remaining number of days (i.e. length of booking period in days less sum of days found in (2) base rate days

re (1), in order find rate periods intersect booking period, note 2 intervals (a,b) , (c,d) have empty intersection iff d < or c > b, can negate condition. should like:

select * rates     not (booking_end < period_start or                booking_start > period_end) 

re (2), need find number of days between max(booking_start, period_start) , min(booking_end, period_end), inclusive. sure there utilities deal dates, in worst case can loop through it.

sorry, not sql/php wizard write actual code... :)