T-SQL select values grouped by week, zero if no values present for week

Question

I am trying to group values (sales data) by week in SQL Server. For items with no sales in a certain week, I still want to get the week number and year, with a sum of 0.

The sales ledger table has computed columns for year and week number, by which I group.

Right now my Query looks like this:

select ItemNumber, sum(Amount), year, week 
from JournalPosition 
group by week, year, ItemNumber 
order by ItemNumber asc, year desc, week desc

What would be an efficient way to accomplish what i want without having to implement a data warehouse? (Stored procedure or temporary table would be fine for me)

Answer 1

You need to generate a list of all of the weeks that you want to include in your query and join onto it. You can either store these in a pre-generated table or use a CTE. Something like this will help you with a CTE how to get the start and end dates of all weeks between two dates in SQL server?

Answer 2

You can use recursive CTE with dates from your table:

declare @StartDate datetime,@EndDate datetime

set @StartDate=(select convert(varchar,min(Year),102) from JournalPosition)
set @EndDate=(select dateadd(day,-1,dateadd(year,2,convert(varchar,max(Year),102))) from JournalPosition)

print @StartDate
print @EndDate

;with CTE as (
  select @StartDate as StartDate, DATEPART(week,@StartDate) as WeekNumber, DATEPART(year,@StartDate) as YearNumber
  union all
  select DATEADD(week, 1, StartDate), DATEPART(week,DATEADD(WEEK, 1, StartDate)), DATEPART(year,DATEADD(week, 1, StartDate))
  from CTE
  where DATEADD(week, 1, StartDate) <= @EndDate
  )
select ItemNumber, isnull(sum(Amount),0), CTE.YearNumber, datepart(week,CTE.StartDate)
from JournalPosition 
full join CTE
on JournalPosition.week=datepart(week,CTE.StartDate) and JournalPosition.year=CTE.YearNumber
group by CTE.YearNumber, datepart(week,CTE.StartDate), ItemNumber
order by 3 desc, 4 desc, 1 asc
option (maxrecursion 32767);

But maybe it's better not to use recursion (see http://www.sqlservercentral.com/Forums/Topic779830-338-1.aspx).