Рассчитать цену с учетом стандартных ставок и сезонных диапазонов дат
Я строю систему стиля бронирования. В комнатах действует стандартная годовая ставка (в этом мой вопрос отличается от других подобных, поскольку у других есть уникальные диапазоны), тогда у них также есть диапазоны дат в году, которые устанавливаются по разным ценам. Я рассчитываю рассчитать цену в MYSQL, но когда пользователь выбирает даты, которые находятся в пределах исключительных дат, он суммирует эту цену, но также суммирует стандартную цену для этих дат, что означает, что итоговая сумма слишком высока. Это становится немного многословно, так что вот запрос и пример:
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 |
+--------+------------+------------+------------+------+
Если пользователь выбирает с 2015-07-15 по 2015-07-25, как в примере в моем запросе, я хочу рассчитать так:
- 2015-07-15 … 2015-07-19 по стандартному тарифу (100 за ночь)
- 2015-07-20 … 2015-07-24 по летнему тарифу (150 за ночь)
Который должен составить 1250. тем не мение Поскольку в стандартной ставке есть даты между первым и последним днем года, запрос также включает эту цену и ставки суммирования из этого диапазона в течение летних дат, что означает, что я получаю все 10 дат, взимаемых по стандартной ставке, плюс 5 ночей в летние цены на общую сумму 1750.
Итак, мой вопрос, как я могу изменить запрос, чтобы использовать только стандартную ставку, если альтернативы нет? Стандартные тарифы всегда называются «Стандартными», поэтому я легко могу их идентифицировать, я просто не знаю, какие изменения внести!
РЕДАКТИРОВАТЬ
Я должен добавить, я хочу сделать это внутри PHP (я использую PDO)
2-й править
Также стоит отметить, что диапазоны дат всегда попадают в один год, и никакие диапазоны дат не могут перекрываться (за исключением стандартной ставки, которая занимает весь год)
РЕШЕНИЕ
Решил изменить мой метод и пойти с ответом, предложенным парой людей, и создать базу данных изо дня в день. Вот мой последний рабочий запрос. Спасибо всем за помощь и предложения.
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
Решение
Я бы использовал таблицу дат: таблицу (datelist) который состоит из одного столбца (date) и содержит все даты предыдущих и последующих N года.
Примерный план запроса (вам может понадобиться правильный дата окончания):
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'
Затем вы можете передать результаты в запрос SUM / GROUP BY.
Другие решения
Используйте оператор MySQL CASE.
например
SELECT Columnname,
sum(CASE
WHEN RangeName LIKE '%Standard%'
THEN do something END) as AliasName
FROM YourTable
То, как я справлялся с подобными проблемами раньше, — это построение таблицы дат.
01.01.1990 .. 01.01.2050 (вы можете сделать это на лету, но из-за нескольких мегабайт памяти прирост производительности по сравнению с «реальной» таблицей делает его целесообразным один раз.
Например.
|Date ID| Date |
|---------------------|
|0000001| 2015-01-01 |
|0000002| 2015-01-02 |
тогда вы можете строить представления, основанные на этом. такие как:
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'
Давая вам вид как
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 (summerPrices> 0, summerPrices, standardPrices)
Не самое элегантное решение, но оно работает, молниеносно и позволяет использовать в запросе объединения, а не циклы, что я предпочитаю.