Estoy intentando crear un informe para un servicio de alojamiento con la siguiente información:

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

Desglosado en cada mes del año.

Limitaciones - Actualmente estoy usando PHP / MySQL para crear este informe. Estoy extrayendo los datos del sistema de reservas 1 mes a la vez, luego uso un proceso ETL para ponerlo en MySQL.
Debido a esto, tengo registros duplicados, cuando una reserva se divide a finales del mes. (Por ejemplo, BookingID = 9216 a continuación: esto se debe a que, para efectos de ingresos, debemos dividir el porcentaje de los ingresos en el mes correspondiente).

La pregunta.

¿Cómo escribo algo de SQL que: Calcule el número de noches de habitación reservadas en una Propiedad y las agrupe por mes. Teniendo en cuenta que si una reserva se extiende a lo largo del final del mes, las noches de habitación que están dentro del mismo mes, como el registro de entrada se cuentan para ese mes, y las noches de habitación que el mismo mes de pago son en el mismo mes como salida

Al principio usé esto: DATEDIFF (Checkout, Checkin). Pero eso llevó a un mes a tener 48 noches de habitación en un mes de 31 días. (porque a) contó 1 reserva como 11 noches, aunque se dividió en los 2 meses, yb) porque aparece dos veces).

Luego, una vez que tenga la declaración, necesito integrarla nuevamente en mi CrossTab SQL para todo el año.

Algunos recursos que he encontrado, pero que no pueden hacer funcionar (Consulta de MySql: intervalo de fechas dentro de un intervalo de fechas & php mysql doble rango de fechas)

Aquí hay una muestra de la tabla: (hay ~ 100,000 filas de datos similares).

<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>