Littlet5621
asked on
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.
This is the basic code i have generated so far,but it does not work:
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:
SELECT dbo.IM_ITEM.ITEM_NO,
dbo.IM_ITEM.DESCR,
dbo.IM_ITEM.CATEG_COD,
dbo.IM_ITEM.SUBCAT_COD,
TOT_QTY_SOLD.ITEM_NO AS Expr1,
TOT_QTY_SOLD.WEEK1_QTY_SOLD
FROM dbo.IM_ITEM
LEFT OUTER JOIN
(SELECT ITEM_NO,
CASE
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'
ELSE 'N'
END AS WEEK1_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
The week definition is confusing. You say week one is 7 days after the current date but in your code you go 7 days before the getdate()-7. Some date sample should help.
ASKER
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.
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.
Isn't the [BUS_DAT Start Date] of datetime already? I see you CAST-ed it.
Wait a sec, is the column name BUS_DAT or [BUS_DAT StartDate]?
ASKER
BUS_DAT is the column name
The Current date is the start date.
Also i removed the cast on BUS_DAT here is what it looks like now.
The Current date is the start date.
Also i removed the cast on BUS_DAT here is what it looks like now.
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_SOLD
FROM 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
Try this:
;
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_WEEKASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am just course why week 52 is set to 728 days and not 364 days
It's just a mistake. You're right, it should be 364.
ASKER
O OK. Thanks for the help Ive been banging my head against a wall for week trying to figure this thing out.
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:
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_WEEKASKER
How do i turn this Query into a view?
>>How do i turn this Query into a view?
:)
add "create view <<a-name-you-choose>> as" at the top, above the select statement
full syntax: http://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx
:)
add "create view <<a-name-you-choose>> as" at the top, above the select statement
full syntax: http://msdn.microsoft.com/en-us/library/ms187956(v=sql.90).aspx
create view vwTotQtyWeeks
as
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
GO
-- to call
select * from vwTotQtyWeeks
You need to look at some pivot type examples, based on having already calculated a dataset of all the data you want in your table....
Problem with SQL pivot is that you need to specifically list the titles of the columns you want creating.
I seem to remember seeing a tutorial someone had managed to create dynamic columns in sql, so that the pivot behaved more like the pivot table option in exel/crystal reports etc...
https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/