Помогите с составлением запроса по подсчёту стоимости

[letsgo]

Помогите с составлением запроса по подсчёту стоимости

Здравствуйте!
Прошу помощи в составлении запроса к таблице БД для подсчёта стоимости тарифа.
Структура таблицы:

id | idtariff |  valid_from  |    valid_to    | date_start     |   date_finish   | price |  
1        5                                       14.07.2010      31.07.2010          25 
2        5        06.07.2010      22.08.2010     29.07.2010      31.07.2010          21 
3        5                                       01.08.2010      22.08.2010          23 
4        5        06.07.2010      22.08.2010     01.08.2010      22.08.2010          20

idtariff - ID тарифа
`valid_from`, `valid_to` - Период, при бронировании в котором действует данная цена (может быть пустым), соответственно Дата Начала и Дата окочания
`date_start`,`date_finish` - Период, в который действует цена на данный тариф
`price` - Стоимость тарифа в сутки



Представим, что сейчас на дворе 1 июля 2010 и мы хотим узнать стоимость тарифа с idtariff=5 c 28.07.2010 по 03.08.2010
Тогда нам необходимо взять в расчёт следующие периоды:
Строка с ID=1 (14.07.2010 31.07.2010): Мы берем 4 дня. 25*4=100
Строка с ID=3 ( 01.08.2010 22.08.2010): Мы берем 3 дня. 23*3=69
Получается стоимость тарифа = 169

Если мы хотим узнать стоимость тарифа, который будет действовать с 28.07.2010 по 03.08.2010,
в промежуток времени между 06.07.2010 и по 22.08.2010, то нам необходимо взять следующие строки:
Строка с ID=1 (14.07.2010 31.07.2010): Мы берем 1 день. 25*1=25
Строка с ID=2 (14.07.2010 31.07.2010): Мы берем 3 дня. 21*3=63
Строка с ID=4 (06.07.2010 22.08.2010): Мы берем 3 дня. 20*3=60
Получается стоимость тарифа = 148

В общем, как понимаете сложностей много (по крайней мере для меня). Как все эти подробности учесть и при этом минимизировать
количество запросов к БД?

Раньше, когда поля `valid_from`, `valid_to` в таблице отсутствовали применялся запрос:

SELECT price, CASE  WHEN date_start < $datestart AND date_finish < $dateend      THEN date_finish - $datestart ... остальные 3 случая по аналогии END as days FROM table WHERE date_start BETWEEN $datestart AND $dateend OR date_finish BETWEEN $datestart AND $dateend

, но тогда в таблице не было пересекающихся дат, т.е. наличие периодов вида 25.07.2010 - 02.08.2010, 01.08.2010 - 20.08.2010 было невозможно. В базе хранились периоды 25.07.2010-31.07.2010 и 01.08.2010-20.08.2010
Сейчас с появлением указанных выше полей пересекающиеся даты появились, что вызывает большие трудности при подсчёте стоимости.


Буду благодарен за помощь и с радостью отвечу на все-все-все вопросы....
Заранее спасибо, коллеги

 

[prolis]

Re: Помогите с составлением запроса по подсчёту стоимости

Автор оригинала: letsgo
Если мы хотим узнать стоимость тарифа, который будет действовать с 28.07.2010 по 03.08.2010,
в промежуток времени между 06.07.2010 и по 22.08.2010...

Брр! Давайте бронировать единовременно, в смысле на день бронирования, в смысле на одну дату бронирования, а не диапазон дат.

 

[letsgo]

Ну давайте...

представим, что сегодня 27.07.2010 и нам нужно узнать стоимость тарифа с 28.07.2010 по 03.08.2010 (Стоимость тарифа нужно определять именно за период, так как минимальное количество суток для закупки: 3
Тогда мы берём:
Строка с ID=1 (14.07.2010 31.07.2010): Мы берем 1 день. 25*1=25
Строка с ID=2 (14.07.2010 31.07.2010): Мы берем 3 дня. 21*3=63
Строка с ID=4 (06.07.2010 22.08.2010): Мы берем 3 дня. 20*3=60
Получается стоимость тарифа = 148

 

[prolis]

Покажу ход своих рассуждений.
1. Находим наши 3 строки:
[sql]
select date_start,date_finish,price from table where
idtariff=5
and '28.07.2010'<date_finish and '03.08.2010'>date_start
and (valid_from is not null and valid_to is not null and '27.07.2010' between valid_from and valid_to)
[/sql] (насчет условия попадания даты брони я мог схалтурить, но не суть, дальше эти даты не участвуют)
2. Умножим их на себя, что бы посмотреть на пересекающие диапазоны (для простоты, не оптимизации для):
[sql]
select t1.date_start,t1.date_finish,t1.price,t1.date_start,t1.date_finish,t1.price from (
select date_start,date_finish,price from table where
idtariff=5
and '28.07.2010'<date_finish and '03.08.2010'>date_start
and (valid_from is not null and valid_to is not null and '27.07.2010' between valid_from and valid_to)
)t1
left join
(
select date_start,date_finish,price from table where
idtariff=5
and '28.07.2010'<date_finish and '03.08.2010'>date_start
and (valid_from is not null and valid_to is not null and '27.07.2010' between valid_from and valid_to)
) t2 on t1.date_finish>t2.date_start
[/sql]
найдется один пересекающийся диапазон:
14.07.2010-31.07.2010 по 25 и
29.07.2010-31.07.2010 по 21
3. Вычисляем из него нужное нам диапазон (опа, тут ещё нет оракловых least и greatest, ну да ладно):
[sql]
select if(t1.date_start>'28.07.2010',t1.date_start,'28.07.2010') as date_start, ifnull(t2.date_start,t1.date_finish) as date_finish
[/sql] (тут я не стал расписывать аналогично старту проверку для вхождения даты окончания, так же через if)
3.В итоге должны получить три строки:
28.07.2010-29.07.2010 по 25
29.07.2010-31.07.2010 по 21
01.08.2010-22.08.2010 по 20
- ага, я забыл вычесть день от 29.07 в первой строчке, тогда
[sql]
ifnull(ADDDATE(t2.date_start,-1),t1.date_finish) as date_finish
[/sql] должны получить:
28.07.2010-28.07.2010 по 25
29.07.2010-31.07.2010 по 21
01.08.2010-22.08.2010 по 20
-а для таких непересеченных интервалов ты косты считать умеешь

 

[letsgo]

WHERE idtariff = 5 AND '28.07.2010' < date_finish AND '03.08.2010' > date_start

Ну у нас же нет в базе периода, который бы четко соответствовал заданному диапазону?
Т.е. в базе должен быть к примеру период: 25.07.2010-15.08.2010, тогда бы что-то нам вернулась...А так возвращается NULL, так как ни одной строки не найден. Или я что-то не догоняю?

 

[prolis]

разберись, почему null, вот для строки id=1:
idtariff = 5 AND '28.07.2010' < '31.07.2010' AND '03.08.2010' > '14.07.2010' - попадает.
а вот тот мой сомнительный отбор по датам брони пока исключи
[sql] AND (
valid_from IS NOT NULL AND valid_to IS NOT NULL AND '27.07.2010'
BETWEEN valid_from AND valid_to
)[/sql] (там неправильно использовать первый AND)