MySQL Pad empty months in summary report. (3/4 of the way there)

Question

This question follows on from: MySQL Number of Days inside a DateRange, inside a month (Booking Table)

I have a table with the following 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);

Then I have this SQL query:

SELECT   
    YEAR(month.d),
    MONTHNAME(month.d),
    IFNULL(SUM(
           DATEDIFF(LEAST(Checkout, LAST_DAY(month.d)), GREATEST(Checkin, month.d))
    ),'0') 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 BETWEEN month.d AND LAST_DAY(month.d) OR month.d BETWEEN Checkin AND Checkout
        )
WHERE  PropertyID = "Mount Olive" 
GROUP BY month.d

And that outputs the following:

+-------------+-------------------+------+
|YEAR(MONTH.D)| MONTHNAME(MONTH.D)| DAYS |
+-------------+-------------------+------+
|2011         | July              | 30   |
|2011         | August            | 15   |
|2011         | September         |  0   |
+-------------+-------------------+------+

Although I would like it to create the following (with the padded empty values):

+-------------+-------------------+------+
|YEAR(MONTH.D)| MONTHNAME(MONTH.D)| DAYS |
+-------------+-------------------+------+
|2011         | January           | 0    |
|2011         | Feburary          | 0    |
|2011         | March             | 0    |
|2011         | April             | 0    |
|2011         | May               | 0    |
|2011         | June              | 0    |
|2011         | July              | 30   |
|2011         | August            | 15   |
|2011         | September         |  0   |
|2011         | October           | 0    |
|2011         | Novemeber         | 0    |
|2011         | December          | 0    |
+-------------+-------------------+------+

What do I need to add to make it do this? Here it is on SQL Fiddle: http://sqlfiddle.com/#!2/c6bdc/2/0

score 1 · Accepted Answer · answered Jun 14 '12 at 22:00

It's joining correctly- the right join is exactly right. The problem is that you're filtering out your results. On rows where the right (month) did not match any left (booking), the Left's fields, including propertyId, will be NULL. NULL is not "2" or "Mount Olive", so the row is excluded.

When checking PropertyId, use an IFNULL/COALESCE:

WHERE  COALESCE(PropertyID, "Mount Olive") = "Mount Olive"

Or make the condition compound: WHERE PropertyID is NULL or PropertyID = "Mount Olive"

Fiddle: http://sqlfiddle.com/#!2/c6bdc/4/0

MySQL Pad empty months in summary report. (3/4 of the way there)

1 Answers1