Hotel_id Room_id Room_type Start_date Конечная цена
-------------------------------------------------- --------------
13 2 standard 2012-08-01 2012-08-15 7000
13 2 standard 2012-08-16 2012-08-31 7500
13 2 standard 2012-09-01 2012-09-30 6000
13 3 люкс 2012-08-01 2012-08-15 9000
13 3 люкс 2012-08-16 2012-08-31 10000
13 3 роскошных 2012-09-01 2012-09-30 9500
Привет, это структура и данные моей таблицы.
Мне нужно создать запрос mysql для бронирования отеля, который будет соответствовать введенным данным в базе данных:
Для Ex:
Если пользователь выбирает отель с роскошным номером на основе этих дат (2012-08-30 – 2012-09-04), общая стоимость будет (10000 * 2) на 30 и 31 августа + (9500 * 3) на 1, 2 и 3-й сентябрь (4-й день проверки не включает), что означает, что общая стоимость будет 20000 + 28500 = 48500
Таким образом, запрос должен фильтровать общую цену на основе Hotel_id, Room_id, Start_date, End_date и Price
благодаря
Используйте это решение:
SELECT SUM( CASE WHEN a.Start_date = b.min_sd AND a.Start_date <> b.max_sd THEN (DATEDIFF(a.End_date, '2012-08-30')+1) * a.Price WHEN a.Start_date = b.max_sd AND a.Start_date <> b.min_sd THEN DATEDIFF('2012-09-04', a.Start_date) * a.Price WHEN (a.Start_date,a.Start_date) IN ((b.min_sd,b.max_sd)) THEN (DATEDIFF('2012-09-04', '2012-08-30')+1) * a.Price WHEN a.Start_date NOT IN (b.min_sd, b.max_sd) THEN (DATEDIFF(a.End_date, a.Start_date)+1) * a.Price END ) AS totalprice FROM rooms a CROSS JOIN ( SELECT MIN(Start_date) AS min_sd, MAX(Start_date) AS max_sd FROM rooms WHERE Room_type = 'luxury' AND End_date >= '2012-08-30' AND Start_date <= '2012-09-04' ) b WHERE a.Room_type = 'luxury' AND a.End_date >= '2012-08-30' AND a.Start_date <= '2012-09-04' Замените события 2012-08-30 и 2012-09-04 с вашими начальными и конечными датами ввода соответственно.
Это будет означать, что начальные и конечные даты будут в том же месяце, а также охватывать несколько месяцев.
Демоверсия SQLFiddle
Вы можете использовать MySQL BETWEEN ... AND ... чтобы найти диапазоны дат, в которые падает желаемый заказ (не забудьте взять один выходной день с указанной даты выписки, так как, как вы говорите, нет ночного пребывания), тогда Группируйте результаты по номеру и принимайте SUM() цены раз количество ночей (которые могут быть рассчитаны с использованием функций MySQL LEAST() и GREATEST() ):
SELECT Room_id, SUM(Price * (1 + DATEDIFF( LEAST(End_date, '2012-09-04' - INTERVAL 1 DAY), GREATEST(Start_date, '2012-08-30') ))) AS Total FROM mytable WHERE Room_type = 'luxury' AND ( '2012-09-04' - INTERVAL 1 DAY BETWEEN Start_date AND End_date OR '2012-08-30' BETWEEN Start_date AND End_date ) GROUP BY Room_id
Смотрите это на sqlfidde .
попробуй это:
set @Hotel_id :=13; set @Room_id :=3; set @Start_date :='2012-08-30' ; set @End_date :='2012-09-04'; select sum(b.TotalPrice-b.deductions) as total_cost from ( select a.Price,a.StartDate,a.EndDate,price*(DATEDIFF(a.EndDate,a.StartDate)+1) as TotalPrice ,case when a.EndDate=@End_date then a.Price else 0 end as deductions from (select price,case when @Start_date>=Start_date then @Start_date else Start_date end as StartDate ,case when @End_date<=End_date then @End_date else End_date end as EndDate from h_booking h1 where Hotel_id=@Hotel_id and Room_id=@Room_id and (@Start_date between Start_date and End_date or @End_date between Start_date and End_date ))a )b