Help with SQL query 1

[mikesoper]

Hi there,

My data looks like this:
PostCode,Product,Month,Sales
AB10,Product1,201106,1212
AB10,Product2,201106,3455
AB11,Product1,201106,3443
AB11,Product2,201106,1344

I need to transform it to do this:

Postcode,Product,Total3MonthSales
AB10,Product1,1121212
AB10,Product2,234234234
AB11,Product1,678678
AB11,Product2,234234

So for each postcode, and each product, I need to take the last 3 months of sales and add them together.

Can anyone help?

Thanks

 

[Qik3Coder]

SELECT Prod_Code, Brick_Code,
AVG(CASE WHEN MonthCounter <= 3 THEN SalesAmount ELSE NULL END) [3Month], 
AVG(CASE WHEN MonthCounter <= 6 THEN SalesAmount ELSE NULL END) [6Month], 
AVG(CASE WHEN MonthCounter = 1 THEN SalesAmount ELSE NULL END) [LastMonth]
FROM (
SELECT Prod_Code, Brick_Code, cast('0' + Number AS Int) SalesAmount, 
DateDiff(mm, cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime), 
CONVERT(varchar(7), GetDate(), 102) + '.01') MonthCounter
FROM dbo.RAW_SALES_RSA31307
WHERE     cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime)
         >= DateAdd(mm, - 6, CONVERT(varchar(7), GetDate(), 102) + '.01')
AND cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime)         
<= DateAdd(mm, - 1, CONVERT(varchar(7), GetDate(), 102) + '.01')
) as a
GROUP BY Brick_Code, Prod_Code
ORDER BY Prod_Code, Brick_Code

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[mikesoper]

Thank you for your support.

I get the 3 and 6 month average, but LastMonth is always null?

Thanks

 

[Qik3Coder]

Do you have values for June?
It is using the month before the current month as last month

I was looking at your last post:
[qoute]
Only thing I dont think its doing is when working out the average it should not take the latest month, so if I have Jan,Feb,Mar,April then 3 month avg should be jan,feb,mar/3 so that I can compare this average with the latest month
[/quote]

You will have to create a ROW_Number instead of doing the date diff on the month. Then you can compare in this fashion.


The base is there. You should be able to take the last couple steps and round out the statement to do what you need.

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[mikesoper]

OK I will try and do that. I am not an expert in this area, so this is probably the most complex query I have been asked to do - all your help has been much appreciated.

 

[Qik3Coder]

It's Friday!

SELECT Prod_Code, Brick_Code,
AVG(CASE WHEN MonthCounter in (2,3,4) THEN SalesAmount ELSE NULL END) [3Month], 
AVG(CASE WHEN MonthCounter in (2,3,4,5,6,7) THEN SalesAmount ELSE NULL END) [6Month], 
AVG(CASE WHEN MonthCounter = 1 THEN SalesAmount ELSE NULL END) [LastMonth]
FROM (
SELECT Prod_Code, Brick_Code, cast('0' + Number AS Int) SalesAmount, 
ROW_NUMBER() OVER (
	Partition by Prod_code, Brick_Code 
	ORDER BY cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime) desc) MonthCounter
FROM dbo.RAW_SALES_RSA31307
WHERE     cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime)
         >= DateAdd(mm, - 6, CONVERT(varchar(7), GetDate(), 102) + '.01')
AND cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime)         
<= DateAdd(mm, - 1, CONVERT(varchar(7), GetDate(), 102) + '.01')
) as a
GROUP BY Brick_Code, Prod_Code
ORDER BY Prod_Code, Brick_Code

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[mikesoper]

Thank you - it says Row_Number is not a recognised function name....

Honestly you have been incredibly helpful, please dont feel you have to continue looking at this as you have given me so much to work from already

 

[Qik3Coder]

What version of SQL are you using?
it will show in the bottom tray of the query window.

You need SQL 2k5 for the ROW_Number function.
There are a couple work arounds, but they all kinda suck.

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[mikesoper]

its 2000!

 

[Qik3Coder]

I told you this would suck:
The while loop is a trick I picked up last year from a stupid SQL2000 CISCO database.

CREATE TABLE #ARRG(
id int identity(1,1),
Prod_Code varchar(50),
Brick_Code varchar(50),
SalesAmount int,
dte datetime,
MonthTicker int)

INSERT INTO #ARRG
SELECT Prod_Code, Brick_Code, cast('0' + Number AS Int) SalesAmount, cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime) dte, 1 MonthTicker
FROM dbo.RAW_SALES_RSA31307
WHERE     cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime)
         >= DateAdd(mm, - 6, CONVERT(varchar(7), GetDate(), 102) + '.01')
AND cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime)         
<= DateAdd(mm, - 1, CONVERT(varchar(7), GetDate(), 102) + '.01')
ORDER BY PROD_CODE, BRICK_CODE

DECLARE @maxid int, @curID int, @ticker int, @Prod_Code varchar(50), @Brick_Code varchar(50)
Select @maxID = (Select Max(id) from #ARRG), @curID = 1, @ticker = 0, @Prod_Code = Prod_Code, @Brick_Code = Brick_Code
FROM #ARRG where id = 1

while(@curID <= @maxid)
 BEGIN
   UPDATE #ARRG
   SET MonthTicker = @ticker + 1
   WHERE id = @curID and Prod_Code = @Prod_Code and Brick_Code = @Brick_Code
   
   Select @ticker = CASE WHEN @Prod_code = Prod_Code AND @Brick_Code = Brick_Code THEN @ticker+1 ELSE 1 END,
   @Prod_code = Prod_Code, @Brick_Code = Brick_Code
   FROM #ARRG
   Where id = @CurID
   
   Select @curID = @curID + 1
 END

SELECT Prod_Code, Brick_Code,
AVG(CASE WHEN MonthTicker in (2,3,4) THEN SalesAmount ELSE NULL END) [3Month], 
AVG(CASE WHEN MonthTicker in (2,3,4,5,6,7) THEN SalesAmount ELSE NULL END) [6Month], 
AVG(CASE WHEN MonthTicker = 1 THEN SalesAmount ELSE NULL END) [LastMonth]
FROM #ARRG
GROUP BY Brick_Code, Prod_Code
ORDER BY Prod_Code, Brick_Code

Lodlaiden

[blue]The doc walks in.[/blue] The good news:[green]"It's just Grumpy Old Man Syndrome."[/green] The bad news:[red]"You're not even 30."[/red]

 

[mikesoper]

Wow that looks intense!

I will try first thing on Monday and let you know!

Have a great weekend