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

 

[r937]

does "last three months" mean calendar months, i.e. from the 1st to the last day of the month? do the last three months include the current month?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[mikesoper]

Hi - thanks for the reply

What I mean is there will be 12 months data - a row for each month and each product and each postcode. So if there 12 rows, each a different month, I need to take the last 3 months/rows but dont include the latest month

What I am actually doing here is I will take the total 3 month number and work out its average, and then compare that with the latest month

Does that make sense?

 

[Qik3Coder]

Your Month column needs to either be a datetime column, or you have to concat '01' to it, so it can be cast as a datetime implicitly.

Select Product, PostCode, AVG(Sales) Sales 
from @Stuff
Where [Month] >= DateAdd(mm, -3, convert(varchar(7), GetDate(), 102)+'.01') 
	and [Month] <= DateAdd(mm, -1, convert(varchar(7), GetDate(), 102)+'.01') 
GROUP BY PostCode, Product
ORDER BY Product, PostCode

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]

This looks good. However all columns are varchar data type - can I convert the month to datetime and sales to numeric at the same time?

Appreciate your help

 

[Qik3Coder]

I figured with the date field. If you just add a '01' to the date it will implicitly convert it to a date.

The sales i would suggest doing a:
AVG(cast ('0'+Sales as Money)) SalesAvg

[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]

I get "syntax error converting datetime from character string"

Also the sales is a sales quantity rather than money value, so I guess I still want numeric?

 

[Qik3Coder]

Warning pseudo code->

Where [Month] >=
becomes:
WHERE cast(right([Month],2)+'/01/'+left([Month],4) as datetime >=

[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]

I am having all sorts of errors! I have pasted the query below - could you check the syntax for me:

SELECT Prod_Code, Brick_Code, AVG(cast('0' + Number AS Money)) SalesAvg
FROM dbo.RAW_SALES_RSA31307
WHERE cast(RIGHT([Month_Code], 2) + '/01/' + LEFT([Month_Code], 4) AS datetime >= DateAdd(mm, - 3, 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')
GROUP BY Brick_Code, Prod_Code
ORDER BY Prod_Code, Brick_Code

Thanks very much.

Brick_code is my post code field
The format of the month_code is yyyymm

 

[Qik3Coder]

There was a missing close paren after the AS datetime

SELECT Prod_Code, Brick_Code, AVG(cast('0' + Number AS Money)) SalesAvg
FROM dbo.RAW_SALES_RSA31307
WHERE 
	cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime) 
		>= DateAdd(mm, - 3, 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')
GROUP BY Brick_Code, Prod_Code
ORDER BY Prod_Code, Brick_Code

That's my fault (sorta <---j/k

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]

Doh! Still get I get "syntax error converting datetime from character string"

 

[Qik3Coder]

That "should" be from one of your fields not having a date in it.

run this:

SELECT DISTINCT RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) FROM dbo.RAW_SALES_RSA31307


What do you have that doesn't look like a date?

[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]

I get a timeout - there are several hundred thousand rows in this table!

 

[gmmastros]

Qik3Coder,

I think your code assumes a MDY dateformat, which may be causing the problem. Since the month column is a varchar and is formatted at YYYYMM, the simplest thing is to concat '01' to it and then convert to DateTime.

If there truly are bad dates in the table, you should be able to find them with this query...

[tt][blue]
Select top 10 *
From dbo.RAW_SALES_RSA31307
Where IsDate(Month_Code + '01') = 0
[/blue][/tt]

Note that SQL Server will never have a problem with date conversions in the format 'YYYYMMDD'.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Qik3Coder]

gmmastros,

Nice on the IsDate...didn't know about that one.
I was thinking it may be a MDY issue, after I had posted.

SELECT a.Prod_Code, a.Brick_Code, AVG(cast('0' + a.Number AS int)) SalesAvg
FROM 
	(SELECT Prod_code, Brick_Code, Number, cast(Month_Code+'01' as datetime) Month_Code
	FROM dbo.RAW_SALES_RSA31307
	WHERE IsDate(Month_code+'01') = 1) as a
WHERE 
	a.Month_Code >= DateAdd(mm, - 3, CONVERT(varchar(7), GetDate(), 102) + '.01') 
	AND a.Month_code <= DateAdd(mm, - 1, CONVERT(varchar(7), GetDate(), 102) + '.01')
GROUP BY a.Brick_Code, a.Prod_Code
ORDER BY a.Prod_Code, a.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]

Hey guys - we are getting there!

I changed to AVG(CAST(NUMBER AS Int)) since this is not money values we are talking about, its quantity.

Can I just check - is it taking the last 3 months, but not including the latest month, to calculate the average?

Could I also add in a 6 month average, but again not count the current month?

Then finally I will need to display the current month.

The idea of this is so that I can compare the latest month for each product, with its 3 and 6 month average

Really appreciate the help

 

[mikesoper]

Any further suggestions guys?

I forgot to say in the post above, that yes indeed there was bad data in the date column!

I really need to try and end up with this view:

Product - Brick_code - 3MonthAvg - 6MonthAvg - LatestMonth

Thanks

 

[Qik3Coder]

What do you have now?(SQL) Does it work? (Yes/No)

[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]

I have this as posted by you yesterday, which works great thanks - I just need to try and add these additional bits on as mentionned above

SELECT Prod_Code, Brick_Code, AVG(cast('0' + Number AS Int)) SalesAvg
FROM dbo.RAW_SALES_RSA31307
WHERE
cast(RIGHT(Month_Code, 2) + '/01/' + LEFT(Month_Code, 4) AS datetime)
>= DateAdd(mm, - 3, 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')
GROUP BY Brick_Code, Prod_Code
ORDER BY Prod_Code, Brick_Code

 

[mikesoper]

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