Estou tentando criar um relatório para um serviço de hospedagem com as seguintes informações:

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

Dividido em cada mês do ano.

Limitações - Atualmente estou usando PHP / MySQL para criar este relatório. Estou tirando os dados do sistema de reservas 1 mês por vez, depois usando um processo ETL para colocá-los no MySQL.
Por causa disso, tenho registros duplicados quando uma reserva é dividida no final do mês. (Por exemplo, BookingID = 9216 abaixo - Isso ocorre porque, para fins de receita, precisamos dividir a porcentagem da receita no mês correspondente).

A questão.

Como faço para escrever algum SQL que irá: Calcular o número de noites que foi reservado em uma propriedade e agrupá-lo pelo mês. Levando em conta que se uma reserva se estende até o final do mês, as diárias que estão dentro do mesmo mês, o check-in são contadas para esse mês e as diárias que são no mesmo mês do check-out no mesmo mês como checkout.

No começo eu usei isso: DATEDIFF (Checkout, Checkin). Mas isso levou a um mês com 48 noites em um mês de 31 dias. (porque a) contava 1 reserva como 11 noites, mesmo que fosse dividida ao longo dos 2 meses eb) porque aparece duas vezes.

Então, quando tiver a declaração, preciso integrá-la novamente no meu SQL CrossTab durante o ano inteiro.

Alguns recursos que eu encontrei, mas não consigo fazer o trabalho (Intervalo de datas de consulta MySql dentro de um intervalo de datas & php mysql intervalo de datas duplo)

Aqui está uma amostra da tabela: (há ~ 100.000 linhas de dados semelhantes).

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