?
Solved

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

Posted on 2013-06-10
15
Medium Priority
?
405 Views
Last Modified: 2013-06-11
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"
Current Table LookWeeks 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.
New View lookI 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

Open in new window

0
Comment
Question by:Littlet5621
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39234638
this is a PIVOT.

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/
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39234702
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.
0
 

Author Comment

by:Littlet5621
ID: 39235606
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.
Current tableI 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.
New Sales View
let me know if you have any more questions.
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 27

Expert Comment

by:Zberteoc
ID: 39235636
Isn't the [BUS_DAT Start Date] of datetime already? I see you CAST-ed it.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39235656
Wait a sec, is the column name BUS_DAT or [BUS_DAT StartDate]?
0
 

Author Comment

by:Littlet5621
ID: 39235658
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.

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

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39235752
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_WEEK

Open in new window

0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 39235788
With this you will see better:

;
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

Open in new window

0
 

Author Comment

by:Littlet5621
ID: 39235811
I am just course why week 52 is set to 728 days and not 364 days
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39235823
It's just a mistake. You're right, it should be 364.
0
 

Author Comment

by:Littlet5621
ID: 39235832
O OK.  Thanks for the help Ive been banging my head against a wall for week trying to figure this thing out.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39235838
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

Open in new window

0
 

Author Comment

by:Littlet5621
ID: 39236126
How do i turn this Query into a view?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39236730
>>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
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 39237741
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

Open in new window

0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question