Insert missing records while executing GROUP BY?

Question

I am trying to use GROUP BY in my table to group my records according to weeks. However, if there are no entries for a specific week, how can I insert 0 rather than a missing entry?

 CREATE TABLE #TEMP (startdate datetime) INSERT INTO #TEMP VALUES('2011-02-01') INSERT INTO #TEMP VALUES('2011-02-02') INSERT INTO #TEMP VALUES('2011-02-03') INSERT INTO #TEMP VALUES('2011-02-04') INSERT INTO #TEMP VALUES('2011-02-05') INSERT INTO #TEMP VALUES('2011-02-18') INSERT INTO #TEMP VALUES('2011-02-19') INSERT INTO #TEMP VALUES('2011-02-20') INSERT INTO #TEMP VALUES('2011-02-21') SELECT DATEPART(YEAR,startdate) AS 'AYear', DATEPART(wk,startdate) AS 'AWeek', COUNT(*) FROM #TEMP GROUP BY DATEPART(YEAR,startdate),DATEPART(wk,startdate) ORDER BY 1,2 DROP TABLE #TEMP 

I get:

 AYear AWeek (No column name) 2011 6 5 2011 8 2 2011 9 2 

but I want this:

 AYear AWeek (No column name) 2011 6 5 2011 7 0 2011 8 2 2011 9 2 

Any suggestions on how to do this?

Answer 1

You can create a table with all possible weeks / years, and then do the following:

 SELECT fw.Year AS 'AYear', fk.Week AS 'AWeek', SUM(CASE WHEN t.startdate is not null then 1 ELSE 0 END) FROM FakeWeeks fw LEFT OUTER JOIN #TEMP t ON DATEPART(YEAR,startdate) = fw.year AND DATEPART(wk,startdate) = fw.week GROUP BY DATEPART(YEAR,startdate),DATEPART(wk,startdate) ORDER BY 1,2 

Or, if you feel economical, create two tables, one over the years and one over 1-52, and join both.