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
387 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
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 26

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
 
LVL 26

Expert Comment

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

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 26

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 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 26

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 26

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 48

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 26

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

IT, Stop Being Called Into Every Meeting

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!

Join & Write a Comment

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 …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now