Rolling Sums

StevenWalsh

I have a table that looks like this:

11
28
18
.
.
.

i'm trying to roll up the values so it would take the value and add it to the previous sum of all the rows above it

11
39
57
.
.
.

I am having the hardest time, any ideas?

Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer. -Fred Brooks

Wendelius

Not sure, but sounds like ROLLUP / GROUPING operations. Have a look at this, it might help you: GROUPING (Transact-SQL)[^]

The need to optimize rises from a bad design. My articles[^]

StevenWalsh

ahhh stupid me, forgot about fetch

DECLARE SalesYearCursor CURSOR FOR
SELECT * FROM #tmpSalesYear

OPEN SalesYearCursor

FETCH NEXT FROM SalesYearCursor
INTO @Percent,@xxxxx,@xxxxx,@xxx

WHILE @@FETCH\_STATUS = 0
BEGIN
	SET @Current = @percent + @previous
	SET @previous = @Current

	UPDATE #tmpSales SET Roll = @Current
	WHERE Percentage = @percent
	AND SalesYear = @year
	
	FETCH NEXT FROM SalesYearCursor
	INTO @Percent,@xxxxx,@xxxxxx,@xxxxxx
END

CLOSE SalesYearCursor

DEALLOCATE SalesYearCursor

EDIT: removed names that identify business :)

Einstein argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer. -Fred Brooks