How to take averages of data and place them into a temp table?

[sebouh181]

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

[Jerry Winston]

Ok. There are a few ways to do this. It sounds like you want the difference of two averages. Because you omitted the comparison of weeks 2 and 3, I think you want the average of an arbitrary pair of weeks NOT the average over a two week interval. I'll give you two solutions. One iterative method using a CURSOR and another using a predefined pair Table.

The cursor method:

DECLARE @baseTable TABLE([month] int, [week] int, price int)
DECLARE @outTable TABLE([month] int, [cWeek] int, [pWeek] int, price int)
DECLARE @cMonth INT
DECLARE @cWeek INT
DECLARE @cPrice INT
DECLARE @pMonth INT
DECLARE @pWeek INT
DECLARE @pPrice INT


SET @cMonth = 0
SET @cWeek = 0
SET @cPrice = 0
SET @pMonth = 0
SET @pWeek = 0
SET @pPrice = 0

INSERT iNTO @baseTable VALUES(1,1,10)
INSERT iNTO @baseTable VALUES(1,1,12)
INSERT iNTO @baseTable VALUES(1,2,16)
INSERT iNTO @baseTable VALUES(1,2,20)
INSERT iNTO @baseTable VALUES(1,3,12)
INSERT iNTO @baseTable VALUES(1,4,16)

DECLARE curT CURSOR 
FOR
SELECT [month],[week],avg([price])[aPrice] FROM @baseTable
GROUP BY [month],[week]
ORDER BY [month],[week]

OPEN curT
FETCH NEXT FROM curT INTO
@cMonth,@cWeek,@cPrice

WHILE (@@FETCH_STATUS = 0)
BEGIN
	if(NOT @pMonth = 0 AND NOT @pWeek = 0)
	BEGIN
		if((@pWeek+1) = @cWeek AND (@cWeek % 2) = 0 AND @pMonth =@cMonth)
		BEGIN
			INSERT iNTO @outTable VALUES( @pMonth,@cWeek,@pWeek,@cPrice-@pPrice)
		END
	END
SET @pMonth=@cMonth
SET @pWeek =@cWeek
SET @pPrice = @cPrice

FETCH NEXT FROM curT INTO
@cMonth,@cWeek,@cPrice
END


SELECT [month],'ResW'+cast(pWeek as varchar(2))+'W'+cast(cWeek as varchar(2)),price FROM @outTable

CLOSE curT
DEALLOCATE curT

The pair table method:

DECLARE @baseTable TABLE([month] int, [week] int, price int)
DECLARE @pairTable TABLE(intervalID int,start int, [stop] int)

INSERT iNTO @baseTable VALUES(1,1,10)
INSERT iNTO @baseTable VALUES(1,1,12)
INSERT iNTO @baseTable VALUES(1,2,16)
INSERT iNTO @baseTable VALUES(1,2,20)
INSERT iNTO @baseTable VALUES(1,3,12)
INSERT iNTO @baseTable VALUES(1,4,16)

INSERT INTO @pairTable values (10,1,2)
INSERT INTO @pairTable values (20,3,4)
INSERT INTO @pairTable values (30,5,6)
INSERT INTO @pairTable values (40,7,8)

SELECT B1.[month],'ResW'+cast([stop] as varchar(2))+'W'+cast([Start] as varchar(2)),B2.aprice-B1.aprice FROM @pairTable I
INNER JOIN
(SELECT [month],[week],avg([price])[aPrice] FROM @baseTable
GROUP BY [month],[week]) B1 ON I.start = B1.week
INNER JOIN
(SELECT [month],[week],avg([price])[aPrice] FROM @baseTable
GROUP BY [month],[week]) B2 ON I.[stop] = B2.week

So, why two code examples? If you were looking for an interval-based solution, I think you could derive your own solution from the Cursor based solution. Alternatively, if you were looking for a comparison of user-defined pairs, the paired-value table might be more useful.

[sebouh181]

I developed my solution based on cursors.

But I have a concern if the cursor has any disadvantage or not.

thanks

[Rabbit]

Depending on how many records you have, cursors can be very slow.