title: Oracle SQL - need dummy date value when there is no data
body: <p>I query the 'sales' table for 'price', grouping by product name:</p>
  
  <pre><code>SELECT product_name, sale_price, sale_date,
  SUM(CASE WHEN sales.sale_date = TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (sale_price) ELSE 0 END) sale_yday,
  SUM(CASE WHEN sales.sale_date = TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 THEN (sale_price) ELSE 0 END) sale_lweek,
  
  -- below is 10 week slope
  -- (N * Sum_XY - Sum_X * Sum_Y)/(N * Sum_X2 - Sum_X * Sum_X)
  ((COUNT(CASE WHEN sale_date &lt; TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (1)
  END) * SUM(CASE WHEN sale_date &lt; TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (TO_NUMBER(TO_CHAR(sale_date, 'J')) * sale_price) END) - 
  SUM(CASE WHEN sale_date &lt; TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (TO_NUMBER(TO_CHAR(sale_date, 'J'))) END) * 
  SUM(CASE WHEN sale_date &lt; TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (sale_price) END))/
  (COUNT(CASE WHEN sale_date &lt; TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (1)
  END) * 
  SUM(CASE WHEN sale_date &lt; TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (POWER(TO_NUMBER(TO_CHAR(sale_date, 'J')),2)) END) - 
  POWER(SUM(CASE WHEN sale_date &lt; TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1 THEN (TO_NUMBER(TO_CHAR(sale_date, 'J'))) END),2))
  ) slope
  
  FROM sales
  WHERE sales.sale_date IN
      (TO_DATE ('14-JUN-14', 'DD-MON-YY'),
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 1,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 2 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 3 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 4 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 5 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 6 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 7 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 8 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 9 * 7,
      TO_DATE ('14-JUN-14', 'DD-MON-YY') - 10 * 7)
  GROUP BY sales.product_name
  </code></pre>
  
  <p>I have an issue, though, whenever there is no data existing for product_name in prior weeks. The slope equation essentially needs to just treat those missing days as 'zero' values, and plug in the correct julian days accordingly as the 'sale_date'. Is there some way that I could check if past sale_date's exist, and if they don't then create 'dummy' rows for it?</p>