0

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');
ScrapeHeap
  • 186
  • 2
  • 11

0 Answers0