Compex Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Compex Query

Author

Topic

sebouh181
Starting Member

1 Post

Posted - 2011-02-01 : 23:35:05

Hi,

I have a table MyTable with the followind Data

Month Week Price
1 1 10
1 1 12
1 2 16
1 2 20
1 3 12
1 4 16

I need to apply this formula (Avg price of W2 - Avg price of W1) and put the result in a temp table and do the same for W4 and W3

So the temp table will be of the following form
Month Week Price
1 ResW1W2 7
1 ResW3W4 4

Any suggestion?

Thanks

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-02 : 00:22:58

declare @tabavgs table(col1 int, col2 int, average int)
declare @tab table(col1 int, col2 int, col3 int)
insert into @tab values (1,1,10),(1,1,12),(1,2,16),(1,2,20),(1,3,12),(1,4,16)

Insert into @tabavgs
Select
Col1
,col2
,AVG(col3) as average
From @tab
Group by col1,col2

Select * from (
Select
A.Col1 as [Month]
,'ResW'+ltrim(str(Col2))+'W'+ltrim(str(A.Col2+1)) as [Week]
,((Select top 1 average from @tabavgs where col2>A.Col2 order by col2)-A.average ) as Price
From @tabavgs A)X where Price>0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-02 : 02:14:31

[code];WITH cteSource(Col1, Col2, Col3)
AS (
SELECT DISTINCT Col1,
Col2,
AVG(1E * Col3) OVER (PARTITION BY Col2) AS Col3
FROM @tab
)
SELECT Col1 AS [Month],
'ResW' + CAST(MIN(Col2) AS VARCHAR(2)) + 'W' + CAST(MAX(Col2) AS VARCHAR(2)) AS [Week],
MAX(CASE WHEN Col2 % 2 = 0 THEN Col3 ELSE 0 END) - MAX(CASE WHEN Col2 % 2 = 1 THEN Col3 ELSE 0 END) AS Price
FROM cteSource
GROUP BY Col1,
(Col2 - 1) / 2[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources