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

Answer 1

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.

Answer 2

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.

Answer 3

Use MySQL CASE statement.

for example

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