recursive_date_orders.sql

recursive_date_orders.sql

WITH RECURSIVE my_dates(x) AS (
    SELECT date('2000-01-01')
        UNION ALL
    SELECT date(x, '+1 day')
    FROM my_dates
    WHERE x < '2030-12-31'
),

all_combos AS (
  SELECT
  x AS CALENDAR_DATE,
  PRODUCT_ID
  FROM PRODUCT
  CROSS JOIN my_dates
  WHERE x BETWEEN '2017-01-01' and '2017-03-31'
),

totals AS (
  SELECT ORDER_DATE,
  PRODUCT_ID,
  SUM(QUANTITY) as TOTAL_QTY

  FROM CUSTOMER_ORDER

  GROUP BY 1, 2
)


SELECT CALENDAR_DATE,
all_combos.PRODUCT_ID,
coalesce(TOTAL_QTY, 0) AS TOTAL_QTY

FROM all_combos LEFT JOIN totals

ON all_combos.CALENDAR_DATE = totals.ORDER_DATE
AND all_combos.PRODUCT_ID = totals.PRODUCT_ID

ORDER BY CALENDAR_DATE, all_combos.PRODUCT_ID