Liczba dni MySQL wewnątrz zakresu DateRange, w ciągu miesiąca (tabela rezerwacji)
Próbuję utworzyć raport dla usługi zakwaterowania z następującymi informacjami:
<code>Number of Bookings (Easy, use the COUNT function) Revenue Amount (Kind of easy). Number of Room nights. (Rather Hard it seems) </code>
Podzielony na każdy miesiąc roku.
Ograniczenia - Obecnie używam PHP / MySQL do tworzenia tego raportu. Wyciągam dane z systemu rezerwacji po jednym miesiącu, a następnie wykorzystuję proces ETL, aby umieścić go w MySQL.
Z tego powodu mam zduplikowane rekordy, gdy rezerwacja dzieli się na koniec miesiąca. (np. BookingID = 9216 poniżej - Dzieje się tak, ponieważ dla celów przychodów musimy podzielić procent przychodów na odpowiedni miesiąc).
Pytanie.
Jak napisać SQL, który: Obliczy liczbę nocy w pokoju, które zostały zarezerwowane w Nieruchomości i Grupuj je w ciągu miesiąca. Biorąc pod uwagę, że jeśli rezerwacja obejmuje cały koniec miesiąca, noce w pokoju w tym samym miesiącu, ponieważ checkin są liczone do tego miesiąca, a noce do pokoju, które są w tym samym miesiącu, w którym realizacja transakcji następuje w tym samym miesiącu jako kasę.
Najpierw użyłem tego: DATEDIFF (Checkout, Checkin). Ale to doprowadziło do tego, że miesiąc miał 48 nocy w 31-dniowym miesiącu. (ponieważ a) liczył 1 rezerwację jako 11 nocy, nawet przez to, że był podzielony na 2 miesiące, ib) ponieważ pojawia się dwa razy).
Potem, gdy już otrzymam oświadczenie, muszę zintegrować je z powrotem w moim CrossTab SQL przez cały rok.
Niektóre zasoby, które znalazłem, ale nie wydają się działać (Zapytanie MySql - zakres dat w zakresie dat & php mysql podwójny zakres dat)
Oto przykład tabeli: (Istnieje ~ 100 000 wierszy podobnych danych).
<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>