MySQL日期范围内的天数（一个月内）（预订表） [英] MySQL Number of Days inside a DateRange, inside a month (Booking Table)
问题描述
预订人数（简单，使用COUNT功能）
收入金额（容易的种类）。
房间数量。 （相当难看）
分解到一年的每个月。
限制 - 我正在使用PHP / MySQL创建此报告。
我一次将数据从预订系统中拉出1个月，然后使用ETL流程将其放入MySQL。
因此，我有重复的记录，当预订分期在本月底。 （例如，BookingID = 9216以下 - 这是因为为了收入目的，我们需要将收入的百分比分配到相应的月份）。
问题。 / b>
如何编写一些SQL：
计算预订到某个房产的房间数量，并将其分组到该月份。考虑到如果预订跨越月底，则在同一个月内的房间晚上，入住日期是在同一个月内，并且同一个月的结帐在同一个月的房间晚上作为结帐。
起初我用这个：DATEDIFF（Checkout，Checkin）。
但是，在31个月的月份里，一个月有48个房间的夜晚。 （因为a）计算1次预订为11晚，即使是在两个月内分开，b）因为出现两次）。
然后一旦我有我需要在整个一年中将其整合到我的CrossTab SQL中。
我发现的一些资源，但似乎无法使工作（http： //stackoverflow.com/questions/6589079/mysql-query-date-range-within-a-date-range& php mysql double date range ）
这是表的一个示例：（有大约100,000行类似的数据） / p>
CREATE TABLE IF NOT EXISTS`bookingdata`（
`idBookingData` int（11）NOT NULL AUTO_INCREMENT，
`PropertyID` int（10）NOT NULL，
`签入日期DEFAULT NULL，
`Checkout`日期DEFAULT NULL，
`Rent`十进制（10,2）DEFAULT NULL，
`BookingID` int（11）DEFAULT NULL，
PRIMARY KEY（`idBookingData`），
UNIQUE KEY`idBookingDa ta_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（ - 添加1，因为同一天的开始和结束仍然是1天
LEAST（结帐，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月ON
签入< = LAST_D AY（month.d）
AND month.d< = Checkout
GROUP BY month.d
请参阅 sqlfiddle 。
I'm attempting to create a report for an accommodation service with the following information:
Number of Bookings (Easy, use the COUNT function)
Revenue Amount (Kind of easy).
Number of Room nights. (Rather Hard it seems)
Broken down into each month of the year.
Limitations - I'm currently using PHP/MySQL to create this report.
I'm pulling the data out of the booking system 1 month at a time, then using an ETL process to put it into MySQL.
Because of this, I have duplicate records, when a booking splits across the end of the Month. (eg BookingID = 9216 below - This is because for Revenue purposes we need to split the percentage of the revenue into the corresponding month).
The Question.
How do I write some SQL that will: Calculate the number of room nights that was booked into a Property and Group it by the month. Taking into account that if a booking spans across the end of the month, that the room nights that are inside of the same month, as the checkin are counted towards that month, and room nights which the same month as checkout are in the same month as checkout.
At first I used this: DATEDIFF(Checkout, Checkin). But that lead to one month having 48 room nights in a 31 day month. (because a) it counted 1 booking as 11 nights, even through it was split across the 2 months, and b) because it appears twice).
Then once I have the statement I need to integrate it back into my CrossTab SQL for the entire year.
Some resources that I have found, but can't seem to make work (http://stackoverflow.com/questions/6589079/mysql-query-date-range-within-a-date-range & php mysql double date range)
Here is a Sample of the Table: (There are ~100,000 rows of similar data).
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);
You're on the right lines. You need to join your query with a table of the months for which you want data, which can either be permanent or (as shown in my example below) created dynamically in a UNION subquery:
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
See it on sqlfiddle.
这篇关于MySQL日期范围内的天数（一个月内）（预订表）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
