Computing number of days which fall between predefined date ranges

Question

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

Answer 1

this is a possible solution algorithm:

1. find all rate periods that have a non-empty intersection with the booking period
2. 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
3. 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... :)

Answer 2

Only a half-serious answer here.

If you want a single SQL query, you could do this:

SELECT SUM(IF(ISNULL(r.rate), 10, r.rate))
FROM days AS d
LEFT JOIN rates AS r ON d.d BETWEEN r.start AND r.end
  WHERE d.d BETWEEN '2011-03-15' and '2011-05-10';

The trick is that you need a table called "days" that has a single date field d. Pre-populate that table to include every day from now until eternity. Simple to do via a script; you only need to keep future bookable dates in there.

Note that you do have to be careful on how you choose the start and end dates. E.g., if you book a room between March 31st and April 1st, what exactly does that mean?

Is that one $20 day and one $10 day? Or only a single $20 day? Or a single $10 day?

It doesn't really affect this answer per se, but it does affect how you build the rates table and which days you use as the start and stop.

Also, this version assumes the rates field uses a full YYYY-MM-DD format. That's not an absolute requirement, but if it's only MM-DD, then you have to modified the join slightly.

Personally, I wouldn't do it like this. Instead, I'd just loop through the days in the PHP code using the DateInterval class and query the price that way.

Answer 3

One probable solution is to have a table called price_periods with a start date, end date and a price and a reference to the room.

So the SQL-table would look like this.

PRICE_PERIODS

PPID | ROOM_ID | PP_START_DATE | PP_END_DATE | PP_PRICE
-------------------------------------------------------
1      2         09-20           10-20         15
2      2         10-21           11-20         20

Then you would simply calculate what dates the booking has been made through, so suppose that Person A want to rent a room from 10-11 until 10-25 he'll have to pay for PRICE_PERIOD with id 1 until PP_END_DATE has occured then pay for PRICE_PERIOD with id 2 until the booking has ended. So the thing is that you'll probably have more than two dates to work with at all times.

I suppose your booking table looks something like this.

BOOKINGS

B_ID | ROOM_ID | USER_ID | START_DATE | END_DATE
------------------------------------------------
1      2         4         10-11        10-25

You'll have to make the calculations but there is a start that would probably work.

Answer 4

Try this.

This loops through each day in the booked period, finds the appropriate rate from the table, and then adds it to the total.

$Day="2011-03-15";
$End="2011-05-10";
$Periods=array(); // Filled with the Mysql rows - mysql_fetch_assoc()
$Total=0;

EDIT

More optimized.

foreach($Periods as $Period) {
    if($Day>=$Period['PERIOD_START']&&$Day<=$Period['PERIOD_END']) {
        while($Day<=$End&&$Day<=$Period['PERIOD_END']) {
            $Total+=$Period['RATE'];
            $Day=date('Y-m-d',strtotime("$Day +1 day"));
        }
    }
}

Answer 5

In PHP you can use DateTime to calculate the difference interval between two dates.