Calculate price given standard rates and seasonal date ranges

Question

I'm building a reservation style system. The rooms have a yearly standard rate (this is where my question differs from other similar ones, because others have unique ranges) then they also have date ranges within the year which are set at different prices. I'm looking to calculate the price in MYSQL but when the user selects dates which are within the exceptional dates ranges, it's summing that price but also summing the standard price for those dates, meaning the total is too high. This is getting a bit wordy so here's the query and an example:

SELECT SUM(Price * (1 + DATEDIFF(LEAST(End_date, '2015-07-25' - INTERVAL 1 DAY), GREATEST(Start_date, '2015-07-15')))) AS Total
FROM room_rates
WHERE roomId = '46' AND (
       '2015-07-25' - INTERVAL 1 DAY BETWEEN Start_date AND End_date 
    OR '2015-07-15'                  BETWEEN Start_date AND End_date
)

+--------+------------+------------+------------+------+
| RoomId | Range Name | Start_Date |  End_Date  | Rate |
+--------+------------+------------+------------+------+
|   46   |  Standard  | 2015-01-01 | 2015-12-31 | 100  |
+--------+------------+------------+------------+------+
|   46   |   Summer   | 2015-07-20 | 2015-08-31 | 150  |
+--------+------------+------------+------------+------+
|   46   |  Christmas | 2015-12-18 | 2015-12-31 | 180  |
+--------+------------+------------+------------+------+

If the user selects 2015-07-15 to 2015-07-25 as in the example in my query, what I want is to calculate as so:

2015-07-15 ... 2015-07-19 at Standard rate (100 per night)
2015-07-20 ... 2015-07-24 at Summer rate (150 per night)

Which should total 1250. However because the Standard rate has dates between the first and last day of the year, the query is also including that price and summing rates from that range during the summer dates, meaning I get all 10 dates charged at standard rate, plus the 5 nights at summer rates, totalling 1750.

So my question is, how can I amend the query to only use the standard rate if no alternative is available? The standard rates are always called "Standard" so I can easily identify them, I just don't know what change to make!

EDIT

I should add, I'm looking to do this inside PHP (I'm using PDO)

2nd Edit Also worth noting, the date ranges will always fall inside a single year, and no date ranges can overlap (with the exception of the standard rate which takes up the entire year)

SOLUTION

Decided to change my method and go with the answer suggested by a couple of people and build up a day by day database. Here is my final, working query. Thanks for everyone's help and suggestions.

SELECT standard_rates.villaId as `villaId`,
            sum(IFNULL(custom_rates.nightly_rate_usd, standard_rates.nightly_rate_usd))
        AS `Rate`
        FROM dates

          LEFT JOIN
            villa_price_bands AS standard_rates
            ON standard_rates.Name = 'Standard'
            AND dates.date BETWEEN standard_rates.Start_Date AND standard_rates.End_Date
            AND FIND_IN_SET(standard_rates.villaId, :resultIds)

          LEFT JOIN
            villa_price_bands AS custom_rates
            ON custom_rates.Name != 'Standard'
            AND dates.date BETWEEN custom_rates.Start_Date AND custom_rates.End_Date
            AND custom_rates.villaId = standard_rates.villaId

        WHERE dates.date >= :arrDate
        AND dates.date < :deptDate
        GROUP BY villaId

I would think the best way is to loop over every day with a cursor (from startDate to endDate), and add the rate to the sum for each date. Otherwise, you will need to split this into N cases, where N is unknown. — Kevin Seifert, Mar 11 '15 at 14:25
That would probably be the better route (easier). The same concept applies. You need a loop that iterates over every day in the date range. Then select the price for each date and add to the sum. — Kevin Seifert, Mar 11 '15 at 14:38
For example: http://stackoverflow.com/questions/3207749/i-have-2-dates-in-php-how-can-i-run-a-foreach-loop-to-go-through-all-of-those-d — Kevin Seifert, Mar 11 '15 at 14:39
I see, so you're saying I'd have to loop in PHP and perform a query for each single day? — ministe, Mar 11 '15 at 14:40
Yes, that would be the most reliable. There's no reliable way to know where the special cases start and stop otherwise (they might switch up the date boundaries next year) ... so you'd just have to check every day. — Kevin Seifert, Mar 11 '15 at 14:42
"loop in PHP and perform a query for each single day" - too many queries. Better get all options from DB with one query, and get prices by comparing in PHP. Always do as less DB queries as possible. — cyadvert, Mar 11 '15 at 14:44
The only other way would be to get the date boundaries and split into N cases... — Kevin Seifert, Mar 11 '15 at 14:44
Ah, see my update - custom date ranges will always fall within the same year so there's no concern about a range starting in 2015 and overlapping into 2016. And no date can have two custom prices, only the standard rate and up to one single custom price — ministe, Mar 11 '15 at 14:51
Still struggling? If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. Don't worry about the PHP part for now. The respondent (us) can choose to ignore or incorporate that part of the problem as they see fit. — Strawberry, Mar 12 '15 at 14:08
@Strawberry, I provided an update, including a fiddle, yesterday. — ministe, Mar 13 '15 at 08:11

Salman A · Accepted Answer · 2015-03-12T14:16:46.493

I would use a table of dates: a table (datelist) that consists of one column (date) and contains all the dates for previous and coming n years.

Rough outline of the query (you might need to correct the end date):

SELECT
    datelist.date AS Night,
    IFNULL(seasonal_rates.Range_Name, standard_rates.Range_Name) AS Season,
    IFNULL(seasonal_rates.Rate, standard_rates.Rate) AS Rate
FROM datelist
LEFT JOIN rates AS standard_rates ON standard_rates.Range_Name = 'Standard'
LEFT JOIN rates AS seasonal_rates ON datelist.date BETWEEN seasonal_rates.Start_Date AND seasonal_rates.End_Date
WHERE standard_rates.RoomId = 46
AND   seasonal_rates.RoomId = 46
AND   datelist.date BETWEEN '2015-07-15' AND '2015-07-25'

You can then pass the results into a SUM/GROUP BY query.

Decided to go down this route in the end. The SQL was much simpler and I managed to iron out the problems that I had with my original query by using this. I've updated my question to include my final working query. Thanks! — ministe, Mar 17 '15 at 09:43

score 0 · Answer 2 · answered Mar 11 '15 at 14:40

0

Use MySQL CASE statement.

for example

SELECT Columnname,
  sum(CASE 
          WHEN RangeName LIKE '%Standard%' 
          THEN do something END) as AliasName
FROM YourTable

answered Mar 11 '15 at 14:40

Abdisamad Khalif

765
2
7
19

if you edit your question and put it your tables structures it will be easy to figure it out, are you using stored stored procedure with parameters to return the above result?, please clarify. Thanks – Abdisamad Khalif Mar 11 '15 at 14:43
the table is very simple and just contains the data in my example: Name, start date, end date, room Id and rate. No stored procedures or anything like that, just the table which sits in a mysql table and the rates are entered by a user through a PHP site. What I'm trying to do is using that data, calculate the total rate based on a date range – ministe Mar 11 '15 at 15:51

score 0 · Answer 3 · answered Mar 11 '15 at 14:45

The way I've dealt with this sort of issue before is to build a 'date' table.

1/1/1990 .. 1/1/2050 (you can do it one the fly, but for the sake of a few Mb of storage the performance gains from a 'real' table make it worth setting up once.

For instance.

|Date ID| Date        |
|---------------------|
|0000001| 2015-01-01  |
|0000002| 2015-01-02  |

then you can built views based on this. such as:

Select 
    [date id], 
    if(range_name='standard',rate,0) standardPrices,
    if(range_name='Summer',rate,0) summerPrices,
    if(range_name='Winter',rate,0) winterPrices  
from 
    date_table left join room_rates 
on date_table.date between start_date and end_date

where room = '46' and date_table.date between '2015-07-15' and '2015-07-25'

Giving you a view like

date        |standardPrices|summerPrices|winterPrices|
------------------------------------------------------
2015-07-15  |       100    |     0      |     0      |
2015-07-16  |       100    |     0      |     0      |
2015-07-17  |       100    |     0      |     0      |
2015-07-18  |       100    |     0      |     0      |
2015-07-19  |       100    |     0      |     0      |
2015-07-20  |       100    |     0      |     0      |
2015-07-20  |       0      |     150    |     0      |
2015-07-21  |       100    |     0      |     0      |
2015-07-21  |       0      |     150    |     0      |

If you THEN group this view by 'Date; you can in turn use it to run queries on to aggregate (again using 'if (summerPrices > 0, summerPrices, standardPrices)

Not the most elegant solution but it works, and is lightning fast and lets you use joins rather than looping in your query, which I prefer.

You definitely don't need a date id. Dates are pretty much unchanging, unique, and invariable. — Strawberry, Mar 13 '15 at 14:07
I don't set the date ranges, they're entirely flexible depending what the owner sets for each one, so I can't group them like this. They might not even bother setting custom time periods, I can only guarantee there'll be one year-long standard price and at most one alternative price for each day. Given that, if I were to go down this date table joining route, how would I give preference to the custom date ranges if there is one for any date? — ministe, Mar 16 '15 at 20:49

Calculate price given standard rates and seasonal date ranges

3 Answers3