Вопрос по mysql, date – MySQL Количество дней внутри DateRange, в течение месяца (таблица бронирования)

2

Я пытаюсь создать отчет для службы размещения со следующей информацией:

<code>Number of Bookings (Easy, use the COUNT function)
Revenue Amount (Kind of easy).
Number of Room nights. (Rather Hard it seems)
</code>

Разбитый на каждый месяц года.

Ограничения. В настоящее время я использую PHP / MySQL для создания этого отчета. Я извлекаю данные из системы бронирования по 1 месяцу, а затем использую процесс ETL, чтобы поместить их в MySQL.
Из-за этого у меня есть дубликаты записей, когда резервирование делится на конец месяца. (например, BookingID = 9216 ниже. Это связано с тем, что для целей дохода нам нужно разделить процент дохода на соответствующий месяц).

The Question.

Как мне написать SQL, который будет: Рассчитайте количество комнатных ночей, забронированных в недвижимость, и сгруппируйте их по месяцам. Принимая во внимание, что если бронирование распространяется на конец месяца, то количество ночей в номере, находящихся в одном и том же месяце, как регистрация, засчитывается в этот месяц, а количество ночей в номере, которые совпадают с датой оформления заказа в том же месяце как оформить заказ.

Сначала я использовал это: DATEDIFF (Checkout, Checkin). Но это привело к тому, что в одном месяце было 48 комнатных ночей в течение 31 дня. (потому что а) он засчитал 1 бронирование как 11 ночей, даже через это было разделено на 2 месяца, и б) потому что он появляется дважды).

Затем, когда у меня есть заявление, мне нужно интегрировать его обратно в CrossTab SQL на весь год.

Некоторые ресурсы, которые я нашел, но, похоже, не работают (MySql Query - диапазон дат в диапазоне дат & Амп;php mysql двойной диапазон дат)

Вот пример таблицы: (Есть ~ 100 000 строк схожих данных).

<code>CREATE TABLE IF NOT EXISTS `bookingdata` (
`idBookingData` int(11) NOT NULL AUTO_INCREMENT,
`PropertyID` int(10) NOT NULL,
`Checkin` date DEFAULT NULL,
`Checkout` date DEFAULT NULL,
`Rent` decimal(10,2) DEFAULT NULL,
`BookingID` int(11) DEFAULT NULL,
PRIMARY KEY (`idBookingData`),
UNIQUE KEY `idBookingData_UNIQUE` (`idBookingData`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10472 ;

INSERT INTO `bookingdata` (`idBookingData`, `PropertyID`, `Checkin`, `Checkout`, `Rent`, `BookingID`) VALUES
(5148, 2, '2011-07-02', '2011-07-05', 1105.00, 10612),
(5149, 2, '2011-07-05', '2011-07-13', 2155.00, 10184),
(5151, 2, '2011-07-14', '2011-07-17', 1105.00, 11102),
(5153, 2, '2011-07-22', '2011-07-24', 930.00, 14256),
(5154, 2, '2011-07-24', '2011-08-04', 1832.73, 9216),
(5907, 2, '2011-07-24', '2011-08-04', 687.27, 9216),
(5910, 2, '2011-08-11', '2011-08-14', 1140.00, 13633),
(5911, 2, '2011-08-15', '2011-08-16', 380.00, 17770),
(5915, 2, '2011-08-25', '2011-08-29', 1350.00, 17719),
(5916, 2, '2011-08-30', '2011-09-01', 740.00, 16813);
</code>

Ваш Ответ

1   ответ
3

яцев, для которых вы хотите получить данные, которые могут быть постоянными или (как показано в моем примере ниже) динамически создаваться в видеUNION подзапрос:

SELECT   YEAR(month.d),
         MONTHNAME(month.d),
         SUM(1 + DATEDIFF( -- add 1 because start&finish on same day is still 1 day
           LEAST(Checkout, LAST_DAY(month.d)), GREATEST(Checkin, month.d)
         )) AS days
FROM     bookingdata
  RIGHT JOIN (
                   SELECT 20110101 AS d
         UNION ALL SELECT 20110201 UNION ALL SELECT 20110301
         UNION ALL SELECT 20110401 UNION ALL SELECT 20110501
         UNION ALL SELECT 20110601 UNION ALL SELECT 20110701
         UNION ALL SELECT 20110801 UNION ALL SELECT 20110901
         UNION ALL SELECT 20111001 UNION ALL SELECT 20111101
         UNION ALL SELECT 20111201
  ) AS month ON
             Checkin <= LAST_DAY(month.d)
         AND month.d <= Checkout
GROUP BY month.d

Посмотри на Sqlfiddle.

Условие соединения можно упростить до:Checkin <= LAST_DAY(month.d) AND month.d <= Checkout ypercubeᵀᴹ
@ ypercube: Хороший вопрос. Обновлено. eggyal
@ Чарли: Для меня это звучит так, как будто вы должны попытаться работать с очищенными данными. Или, если каждая запись была вставлена дважды, вы могли бы просто наполовину получить результат? К вашему сведению, вы должны принять ответы если они решат твою проблему и Upvote они, если они полезны или полезны. eggyal
Большое спасибо, это самое близкое, что я получил за всю неделю. Поскольку это действительно раздражающее удвоение строк, возможно ли, что он будет использовать какой-то тип SELECT DISTINCT в столбце BookingID или в столбце Checkin / CheckOut. То, что происходит, - то, что Ряд добавляется дважды. Так, например, в июле месяце есть бронирование на 11 ночей с 2011-07-24 по 2011-08-04 Первые 7 дней бронирования в июле добавляются правильно, но дважды. Или я должен очистить данные в процессе ETL? (столбец Аренда используется для каждого месяца). Charlie
Спасибо, удваиваются только заказы за месяц. Данные извлекаются помесячно, с использованием «Отчета о выписке» - это был единственный отчет с необходимыми данными из программного обеспечения. (Собственная база данных также). Таким образом, если бронирование падает в течение месяца, владельцу выплачивается только тот процент бронирования, который выпадает в течение этого месяца. Затем остаток выплачивается в следующем месяце. Отсюда и удвоение. Я мог бы посмотреть на объединение строк в процессе ETL. Charlie

Похожие вопросы