Aggregate calculations in a While loop

[rogerzebra]

good evening folks,
I have an issue with a loop and I was hoping for some input from any of you guys, that would be greately appreciated. I'm trying to update an empty tmp column with the average value of a product for each year, one year at the time. This should be fairly simple but, I can't get around this issue and apparently need your input.

First it was a while ago I was using loops so that might be the reason, second I tried to use a set based statement due to some suggestions on this forum but, got nasty errors indicating that it doesn't seems to like aggregates as a set value. Unfortunately, I'm not at work so I can't provide you with my original query but, hopefully you can tell from my improvised two minute code, what I'm trying to acchive. I just want to get a ruff idea of how to deal with aggregates and calculations in an insert loop and insert one row by one.
Thanks
/r

declare 	@Id int,
	@AvgValue money,
 	@r1 int,
	@r2 int,
set	@r1 = (select max(id) from tmp)
set	@r2 = (select avg(retail)/max(retail) from tmp)
	

create table tmp (Id int, AvgValue money)
	set @Id = 0
	while @Id < @r1
	begin
	  set @value = @r2 + 1
	      insert into tmp values(@Id, @AvgValue )
	  end  
	  select * from tmp
	drop table tmp

 

[ESquared]

second I tried to use a set based statement due to some suggestions on this forum

That was good advice. Switching to the loop method is bad.

Post the set-based method you tried that gave you errors. All you need to do is put this in a derived table, join to it and do the update. But I'm not going to convert your loop thing into a join. You do that and someone will help you get it working.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]