I feel like I am about 90% of the way to figuring this out and it probably just needs some kind of sub-query but I don't know what it would look like.
I followed the guide on this website and got the expected result just fine with this query:
SELECT calendar_dates.cdate, IFNULL(SUM(daily_inv_history.quantity),0) AS daily_inv
FROM daily_inv_history
RIGHT JOIN calendar_dates
ON (DATE(daily_inv_history.snapshot_date) = calendar_dates.cdate)
WHERE (
calendar_dates.cdate BETWEEN (
SELECT MIN(DATE(snapshot_date)) FROM daily_inv_history)
AND (SELECT MAX(DATE(snapshot_date)) FROM daily_inv_history))
GROUP BY calendar_dates.cdate
I get this correct result, where the days with a sum of 0 are still returned:
cdate daily_inv
2019-08-15 4
2019-08-16 1
2019-08-17 0
2019-08-18 0
2019-08-19 22
But then when I try to add another WHERE condition by changing the WHERE to this:
WHERE (
daily_inv_history.sku='skuA', AND
calendar_dates.cdate BETWEEN (
SELECT MIN(DATE(snapshot_date)) FROM daily_inv_history)
AND (SELECT MAX(DATE(snapshot_date)) FROM daily_inv_history))
I get this result where the days with a sum of 0 are no longer present. SQLFiddle can be found here.
cdate daily_inv
2019-08-15 4
2019-08-16 1
2019-08-19 12
How do I make it so that the dates where the daily_inv is 0 are still returned with the added conditional?
EDIT: I tried the suggestion to put daily_inv_history.sku='skuA'
in the JOIN ... ON but this led to extremely poor performance on the real db. Any other ideas?
Here is the schema and example table creation code:
CREATE TABLE `daily_inv_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`snapshot_date` date NOT NULL,
`sku` varchar(45) NOT NULL,
`quantity` int(11) NOT NULL,
`fulfillment_center_id` varchar(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uc_daily_inv_hist` (`snapshot_date`,`sku`,`fulfillment_center_id`)
) ENGINE=InnoDB AUTO_INCREMENT=924974 DEFAULT CHARSET=utf8;
INSERT INTO daily_inv_history (snapshot_date, sku, quantity, fulfillment_center_id)
VALUES
('2019-08-15 07:00:00', 'skuA', '2', 'fc1'),
('2019-08-15 07:00:00', 'skuA', '2', 'fc2'),
('2019-08-16 07:00:00', 'skuA', '1', 'fc1'),
('2019-08-19 07:00:00', 'skuA', '5', 'fc1'),
('2019-08-19 07:00:00', 'skuA', '7', 'fc2'),
('2019-08-19 07:00:00', 'skuB', '10', 'fc2');
CREATE TABLE `calendar_dates` (
`cdate` date DEFAULT NULL,
KEY `calendar_dates_idx_cdate` (`cdate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO calendar_dates (cdate)
VALUES
('2019-08-13'),
('2019-08-14'),
('2019-08-15'),
('2019-08-16'),
('2019-08-17'),
('2019-08-18'),
('2019-08-19'),
('2019-08-20');