Create SQL view That groups on column A then creates column D from sum column B if in date range of date column C

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.

Thanks for the reply s
a i believe Sudonim is correct about the pivot and have went back and tried but my big problem selecting the dates properly to produce the total sales for each week. Below i went into greater detail about the weeks let me know if this helps make more sense.

This table is showing all sales between 2013-05-31 and 2013-05-01 for these two item numbers.I want to show in WEEK1_QTY_SOLD all sales between the current date (2013-05-31) to seven days prior to the current date (2013-05-24). Then for WEEK2_QTY_SOLD I want to show sales between eight days prior to current date (2013-05-25) to fourteen days prior to current date (2013-05-17). WEEK3_QTY_SOLD would represent the total number of sales between the fifteenth days prior to the current date (2013-05-16) to twenty two days prior to the current date (2013-05-10). This would go on until i got fifty two weeks worth of sales history.
The view would look like this in the end.
let me know if you have any more questions.

SELECT dbo.IM_ITEM.ITEM_NO, dbo.IM_ITEM.DESCR, dbo.IM_ITEM.CATEG_COD, dbo.IM_ITEM.SUBCAT_COD,TOT_QTY_SOLD.WEEK1_QTY_SOLDFROM dbo.IM_ITEM LEFT OUTER JOIN (SELECT ITEM_NO, SUM(CASE WHEN BUS_DAT BETWEEN CAST(GETDATE() AS DATETIME) AND CAST(GETDATE()-7 AS DATETIME) THEN QTY_SOLD ELSE 0 END) WEEK1_QTY_SOLD, SUM(CASE WHEN BUS_DAT BETWEEN CAST(GETDATE()-8 AS DATETIME) AND CAST(GETDATE()-14 AS DATETIME) THEN QTY_SOLD ELSE 0 END) WEEK2_QTY_SOLD, SUM(CASE WHEN BUS_DAT BETWEEN CAST(GETDATE()-15 AS DATETIME) AND CAST(GETDATE()-21 AS DATETIME) THEN QTY_SOLD ELSE 0 END) WEEK3_QTY_SOLD, SUM(CASE WHEN BUS_DAT BETWEEN CAST(GETDATE()-22 AS DATETIME) AND CAST(GETDATE()-28 AS DATETIME) THEN QTY_SOLD ELSE 0 END) WEEK4_QTY_SOLD, FROM dbo.PS_TKT_HIST_LIN GROUP BY ITEM_NO,BUS_DAT) AS TOT_QTY_SOLD ON dbo.IM_ITEM.ITEM_NO = TOT_QTY_SOLD.ITEM_NO

;WITH QTY_WEEK as( SELECT ITEM_NO, CASE WHEN datediff(DD,[BUS_DAT], GETDATE()) <=7 then 'Week_01' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=14 then 'Week_02' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=21 then 'Week_03' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=28 then 'Week_04' -- and so on using a multiple of 7 for each week up to 52 weeks --WHEN datediff(DD,[BUS_DAT], GETDATE()) <=728 then 'Week_52' ELSE NULL END AS SALE_WEEK, QTY_SOLD FROM dbo.PS_TKT_HIST_LIN),TOT_QTY_WEEK as ( SELECT ITEM_NO, [Week_01], [Week_02], [Week_03], [Week_04] -- ... [Week_52] FROM QTY_WEEK PIVOT(SUM(QTY_SOLD) FOR SALE_WEEK IN ([Week_01],[Week_02],[Week_03],[Week_04]/* , ..., [Week_52] */)) as pvt)select * from TOT_QTY_WEEK

;WITH QTY_WEEK as( SELECT ITEM_NO, CASE WHEN datediff(DD,[BUS_DAT], GETDATE()) <=7 then 'Week_01' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=14 then 'Week_02' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=21 then 'Week_03' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=28 then 'Week_04' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=35 then 'Week_05' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=42 then 'Week_06' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=49 then 'Week_07' -- and so on using a multiple of 7 for each week up to 52 weeks --WHEN datediff(DD,[BUS_DAT], GETDATE()) <=728 then 'Week_52' ELSE NULL END AS SALE_WEEK, QTY_SOLD FROM dbo.PS_TKT_HIST_LIN),TOT_QTY_WEEK as ( SELECT ITEM_NO, isnull([Week_01],0) as [Week_01], isnull([Week_02],0) as [Week_02], isnull([Week_03],0) as [Week_03], isnull([Week_04],0) as [Week_04], isnull([Week_05],0) as [Week_05], isnull([Week_06],0) as [Week_06], isnull([Week_07],0) as [Week_07] -- ... ISNULL([Week_52],0) as [Week_52] FROM QTY_WEEK PIVOT(SUM(QTY_SOLD) FOR SALE_WEEK IN ([Week_01],[Week_02],[Week_03],[Week_04],[Week_05],[Week_06],[Week_07]/* , ..., [Week_52] */)) as pvt)select * from TOT_QTY_WEEK

One more thing. If you are only interested in the sales in the last year I recommend to put a filter on BUS_DATE for dates within the last year. Also you should make sure that you have an index on that column. This will help with performance.

Here is the updated code:

;WITH QTY_WEEK as( SELECT ITEM_NO, CASE WHEN datediff(DD,[BUS_DAT], GETDATE()) <=7 then 'Week_01' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=14 then 'Week_02' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=21 then 'Week_03' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=28 then 'Week_04' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=35 then 'Week_05' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=42 then 'Week_06' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=49 then 'Week_07' -- and so on using a multiple of 7 for each week up to 52 weeks --WHEN datediff(DD,[BUS_DAT], GETDATE()) <=728 then 'Week_52' ELSE NULL END AS SALE_WEEK, QTY_SOLD FROM dbo.PS_TKT_HIST_LIN WHERE [BUS_DAT]>=DATEADD(YY, -1, GETDATE())),TOT_QTY_WEEK as ( SELECT ITEM_NO, isnull([Week_01],0) as [Week_01], isnull([Week_02],0) as [Week_02], isnull([Week_03],0) as [Week_03], isnull([Week_04],0) as [Week_04], isnull([Week_05],0) as [Week_05], isnull([Week_06],0) as [Week_06], isnull([Week_07],0) as [Week_07] -- ... ISNULL([Week_52],0) as [Week_52] FROM QTY_WEEK PIVOT(SUM(QTY_SOLD) FOR SALE_WEEK IN ([Week_01],[Week_02],[Week_03],[Week_04],[Week_05],[Week_06],[Week_07]/* , ..., [Week_52] */)) as pvt)select * from TOT_QTY_WEEK

create view vwTotQtyWeeksasWITH QTY_WEEK as( SELECT ITEM_NO, CASE WHEN datediff(DD,[BUS_DAT], GETDATE()) <=7 then 'Week_01' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=14 then 'Week_02' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=21 then 'Week_03' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=28 then 'Week_04' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=35 then 'Week_05' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=42 then 'Week_06' WHEN datediff(DD,[BUS_DAT], GETDATE()) <=49 then 'Week_07' -- and so on using a multiple of 7 for each week up to 52 weeks --WHEN datediff(DD,[BUS_DAT], GETDATE()) <=728 then 'Week_52' ELSE NULL END AS SALE_WEEK, QTY_SOLD FROM dbo.PS_TKT_HIST_LIN WHERE [BUS_DAT]>=DATEADD(YY, -1, GETDATE())),TOT_QTY_WEEK as ( SELECT ITEM_NO, isnull([Week_01],0) as [Week_01], isnull([Week_02],0) as [Week_02], isnull([Week_03],0) as [Week_03], isnull([Week_04],0) as [Week_04], isnull([Week_05],0) as [Week_05], isnull([Week_06],0) as [Week_06], isnull([Week_07],0) as [Week_07] -- ... ISNULL([Week_52],0) as [Week_52] FROM QTY_WEEK PIVOT(SUM(QTY_SOLD) FOR SALE_WEEK IN ([Week_01],[Week_02],[Week_03],[Week_04],[Week_05],[Week_06],[Week_07]/* , ..., [Week_52] */)) as pvt)select * from TOT_QTY_WEEKGO-- to callselect * from vwTotQtyWeeks

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:
ht…

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …