I am trying to create a sales history view table that groups data on the Item number then crates a column per week that finds the sum of the qty sold for up to a year. Here is a example of our current data.
How the data looks in the table.
How i want the view table to look
Current date "2013-05-31"
Weeks are not based on calendar week they are based on 7 days after current date for week one then 7 days after end of week two and so on and so forth. if data does not exist for that week it needs to put 0 for that week.
I will be joining this to our ITEM table so all our items appear no matter if they where sold in this years time or not.
This is the basic code i have generated so far,but it does not work:
TOT_QTY_SOLD.ITEM_NO AS Expr1,
LEFT OUTER JOIN
WHEN CAST([PS_TKT_HIST_LIN.BUS_DAT Start Date] AS DATE) < CAST(GETDATE AS DATE) AND CAST([BUS_DAT Start Date] AS DATE) > CAST(GETDATE() - 7 AS DATE) THEN 'Y'
END AS WEEK1_QTY_SOLD
GROUP BY ITEM_NO,
BUS_DAT) AS TOT_QTY_SOLD ON dbo.IM_ITEM.ITEM_NO = TOT_QTY_SOLD.ITEM_NO