· 7 years ago · Sep 12, 2018, 02:56 PM
1MySQL Pad empty months in summary report. (3/4 of the way there)
2CREATE TABLE IF NOT EXISTS `bookingdata` (
3`idBookingData` int(11) NOT NULL AUTO_INCREMENT,
4`PropertyID` int(10) NOT NULL,
5`Checkin` date DEFAULT NULL,
6`Checkout` date DEFAULT NULL,
7`Rent` decimal(10,2) DEFAULT NULL,
8`BookingID` int(11) DEFAULT NULL,
9PRIMARY KEY (`idBookingData`),
10UNIQUE KEY `idBookingData_UNIQUE` (`idBookingData`)
11) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10472 ;
12
13INSERT INTO `bookingdata` (`idBookingData`, `PropertyID`, `Checkin`, `Checkout`, `Rent`, `BookingID`) VALUES
14(5148, 2, '2011-07-02', '2011-07-05', 1105.00, 10612),
15(5149, 2, '2011-07-05', '2011-07-13', 2155.00, 10184),
16(5151, 2, '2011-07-14', '2011-07-17', 1105.00, 11102),
17(5153, 2, '2011-07-22', '2011-07-24', 930.00, 14256),
18(5154, 2, '2011-07-24', '2011-08-04', 1832.73, 9216),
19(5907, 2, '2011-07-24', '2011-08-04', 687.27, 9216),
20(5910, 2, '2011-08-11', '2011-08-14', 1140.00, 13633),
21(5911, 2, '2011-08-15', '2011-08-16', 380.00, 17770),
22(5915, 2, '2011-08-25', '2011-08-29', 1350.00, 17719),
23(5916, 2, '2011-08-30', '2011-09-01', 740.00, 16813);
24
25SELECT
26 YEAR(month.d),
27 MONTHNAME(month.d),
28 IFNULL(SUM(
29 DATEDIFF(LEAST(Checkout, LAST_DAY(month.d)), GREATEST(Checkin, month.d))
30 ),'0') AS Days
31FROM bookingdata
32RIGHT JOIN (
33 SELECT 20110101 AS d
34 UNION ALL SELECT 20110201 UNION ALL SELECT 20110301
35 UNION ALL SELECT 20110401 UNION ALL SELECT 20110501
36 UNION ALL SELECT 20110601 UNION ALL SELECT 20110701
37 UNION ALL SELECT 20110801 UNION ALL SELECT 20110901
38 UNION ALL SELECT 20111001 UNION ALL SELECT 20111101
39 UNION ALL SELECT 20111201
40 ) AS month ON (
41 Checkin BETWEEN month.d AND LAST_DAY(month.d) OR month.d BETWEEN Checkin AND Checkout
42 )
43WHERE PropertyID = "Mount Olive"
44GROUP BY month.d
45
46+-------------+-------------------+------+
47|YEAR(MONTH.D)| MONTHNAME(MONTH.D)| DAYS |
48+-------------+-------------------+------+
49|2011 | July | 30 |
50|2011 | August | 15 |
51|2011 | September | 0 |
52+-------------+-------------------+------+
53
54+-------------+-------------------+------+
55|YEAR(MONTH.D)| MONTHNAME(MONTH.D)| DAYS |
56+-------------+-------------------+------+
57|2011 | January | 0 |
58|2011 | Feburary | 0 |
59|2011 | March | 0 |
60|2011 | April | 0 |
61|2011 | May | 0 |
62|2011 | June | 0 |
63|2011 | July | 30 |
64|2011 | August | 15 |
65|2011 | September | 0 |
66|2011 | October | 0 |
67|2011 | Novemeber | 0 |
68|2011 | December | 0 |
69+-------------+-------------------+------+
70
71WHERE COALESCE(PropertyID, "Mount Olive") = "Mount Olive"