Write Forums

Add weeks in a query?

Posted on 16th Feb 2014 07:03 pm by admin

I have a SQL that summarizes the quantity per week. The weeks that has no value does not exist in the table. How can I add these missing weeks with a 0 value in the SQL. Need this to use Avg and stddev functions.
SELECT t.material, t.description, Sum(t.sales_quantity ) qty, to_char(t.Posting_Date,'IYYYIW') year_week FROM tblBilling t Where t.material = 'label900' And t.Posting_date >= to_date('2008-10-20','yyyy-mm-dd') And t.Posting_date <= to_date('2009-10-20','yyyy-mm-dd') Group By t.material, t.description, to_char(t.Posting_Date,'IYYYIW')

This return:
material descriotion qty year_week label900 test 20000 200850 label900 test 10000 200910 label900 test 5000 200912 label900 test 6000 200914 label900 test 9000 200920
I want it to complement the missing weeks with 0. Like this:
material descriotion qty year_week label900 test 0 200843 label900 test 0 200844 label900 test 0 200845 label900 test 0 200846 label900 test 0 200847 label900 test 0 200848 label900 test 0 200849 label900 test 20000 200850 label900 test 0 200851 --and so on