Selecting date range from price matrix

[BenWeston]

Hi all

This is a one I've spent DAYS on and can't figure out  This is for a wedding venue and their pricing schedule is divided into low, medium, high and very high seasons. I.e. low season runs from 8 Jan 16 to 28 Feb 16, medium runs from 4 Mar 16 to 20 Mar 16, etc.

The database for this is laid out as attached.

 

I would like people to be able to enter their arrival and departure date and for it to be able to choose which seasons the date range falls under. I.e. If they stay 8 to 12 Jan, that's no problem to calculate and falls under LOW season. However, if they stay 27 Feb to 10 Mar, this falls under both LOW and MID seasons, with the majority of the stay in the MID season (which we'd price with but can use PHP to choose the 'highest' season from the array).

I'm a bit stumped on this one so any help is hugely appreciated! I'm sure it's something quite simple that I'm missing.

Edited January 10, 2015 by BenWeston

[requinix]

SELECT every season where its from_date is on or before your end date and its to_date is on or after your start date. I can explain why that works if you want.

So

SELECT * FROM table WHERE from_date = '2016-02-27' ORDER BY from_date

If you try that query in your head you'll see it returns the first two rows. You can also use some date functions in the query to immediately tell you how much overlap there is using DATEDIFF.

DATEDIFF(LEAST('2016-03-10', to_date), GREATEST(from_date, '2016-02-27')) + 1

(+1 assuming you count the end date as part of the stay; Jan 1 to Jan 2 would be two days, not one)

 

[edit] While I'm here, how about getting rid of the "year" column? You already have that information with the two dates.

Edited January 10, 2015 by requinix

[BenWeston]

Hi Requinix. Thanks for the reply. Not sure how you implement DATEDIFF within the first query and what it actually achieves?

[BenWeston]

Essentially, if possible, it would be great for the query to spit out each season 'band' the date range falls into and how many days of each it covers. I can then calculate the rate based on, say, 3 days @ medium season, 10 days @ high season, 2 days @ easter season, for example.

Edited January 11, 2015 by BenWeston

[Barand]

What happens for those days that fall between the dates in your season table, the date bands are not contiguous?

[BenWeston]

Hi Barand. That's my other issue. There are two tables - this one covers WEEKEND prices, the other covers MIDWEEK prices and fills in those gaps. It's hugely complex and I'm beginning to wonder if what they want me to do is even possible.

 

As I can see it, the calculator will need to take the date range, see how many 'seasons' it covers, work out how many days it covers each seasons for so that it can work out how many Fri - Sun are in that range, refer to the other table to get the pricing for that, price up the rest of the days on the midweek prices and spit out the total.

 

It seems monstrously complex. And I need it finished for tomorrow. Typical.

[Barand]

I'd have a single table with contiguous dates and also include a column for midweek price and another for the additional weekend premium. Also, when making a booking I would generate a booking record for each day of the stay.

 

Then it's easy to "JOIN  ON booking.date BETWEEN price.startdate AND price.enddate" and pick up the rate, applying the premium for dates that fall on weekend

[Barand]

Example of the above

mysql> SELECT * FROM rate;
+---------+------------+------------+--------+--------+-----------+
| rate_id | from_date  | to_date    | season | rate   | wepremium |
+---------+------------+------------+--------+--------+-----------+
|       1 | 2016-01-08 | 2016-02-28 | LOW    | 100.00 |     25.00 |
|       2 | 2016-02-29 | 2016-03-20 | MID    | 110.00 |     25.00 |
|       3 | 2016-03-21 | 2016-04-10 | EASTER | 150.00 |     50.00 |
+---------+------------+------------+--------+--------+-----------+

mysql> SELECT * FROM booking_days;
+------------+------------+
| booking_id | date       |
+------------+------------+
|       1234 | 2016-02-27 |
|       1234 | 2016-02-28 |
|       1234 | 2016-02-29 |
|       1234 | 2016-03-01 |
|       1234 | 2016-03-02 |
|       1234 | 2016-03-03 |
|       1234 | 2016-03-04 |
|       1235 | 2016-03-19 |
|       1235 | 2016-03-20 |
|       1235 | 2016-03-21 |
|       1235 | 2016-03-22 |
|       1235 | 2016-03-23 |
+------------+------------+

SELECT booking_id
, DATE_FORMAT(date, '%a %D %b %Y') as date
, season
, SUM(r.rate + CASE WHEN DAYOFWEEK(b.date) IN (1,6,7) THEN r.wepremium ELSE 0 END)
    as price
FROM booking_days b
    JOIN rate r ON b.date BETWEEN r.from_date and r.to_date
GROUP BY booking_id, date 
WITH ROLLUP;

+------------+-------------------+--------+---------+
| booking_id | date              | season | price   |
+------------+-------------------+--------+---------+
|       1234 | Sat 27th Feb 2016 | LOW    |  125.00 |
|       1234 | Sun 28th Feb 2016 | LOW    |  125.00 |
|       1234 | Mon 29th Feb 2016 | MID    |  110.00 |
|       1234 | Tue 1st Mar 2016  | MID    |  110.00 |
|       1234 | Wed 2nd Mar 2016  | MID    |  110.00 |
|       1234 | Thu 3rd Mar 2016  | MID    |  110.00 |
|       1234 | Fri 4th Mar 2016  | MID    |  135.00 |
+------------+-------------------+--------+---------+
|       1234                              |  825.00 |
+------------+-------------------+--------+---------+
|       1235 | Sat 19th Mar 2016 | MID    |  135.00 |
|       1235 | Sun 20th Mar 2016 | MID    |  135.00 |
|       1235 | Mon 21st Mar 2016 | EASTER |  150.00 |
|       1235 | Tue 22nd Mar 2016 | EASTER |  150.00 |
|       1235 | Wed 23rd Mar 2016 | EASTER |  150.00 |
+------------+-------------------+--------+---------+
|       1235                              |  720.00 |
+------------+-------------------+--------+---------+
|      TOTAL                              | 1545.00 |
+------------+-------------------+--------+---------+