我正在尝试使用以下信息创建住宿服务报告：

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

细分为一年中的每个月.

限制-我目前正在使用PHP / MySQL创建此报告.
我一次将数据从预订系统中提取一次,然后使用ETL流程将其放入MySQL.
因此,当预订在月末拆分时,我有重复的记录. (例如,下面的BookingID = 9216-这是因为出于“收入”的目的,我们需要将收入的百分比分成相应的月份).

问题.

我该如何编写一些SQL：
计算预订到一个属性中的房晚数,然后按月将其分组.考虑到如果预订跨越了月底,则当月的入住天数(即签到)将计入当月,而当月的结账天数将计入当月作为结帐.

最初,我使用了这个：DATEDIFF(Checkout,Checkin).
但这导致一个月的31天中有48个晚上. (因为a)将1笔预订算作11个晚上,即使这笔预订被划分为两个月,b)因为它出现了两次).

然后,一旦有了该声明,我就需要将其整年重新集成到我的CrossTab SQL中.

我找到了一些资源,但似乎无法正常工作(MySql Query- Date Range within a Date Range& php mysql double date range)

这是表的样本：(大约有100,000行类似数据).

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

解决方法:

您处在正确的位置.您需要将查询与需要数据的月份的表连接起来,该表可以是永久性的,也可以是(在下面的示例中显示)在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上看到它.