MySQL Количество дней внутри DateRange, в течение месяца (таблица бронирования)
Я пытаюсь создать отчет для службы размещения со следующей информацией:
<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>