Running Values Strategy

Question

Running Values Strategy

stubob

SSCommitted

Points: 1839
More actions
December 10, 2003 at 11:02 am

#107025
The problem is creating a "running value" - calculating the result in the current row, based on the result in the previous row, which in turn is calculated from the row before, etc...
It would be nice if calculated columns could handle this, but it seems to me they only work for calculations involving values on the same row in the table.
Here's an example of the problem:
Table1:
nDaySalesSumMV
1150150150.00
2200350160.00
3120470152.00
4165635154.60
5220855167.68
The table is sorted by Day, R_Sum is a running sum, and MV is moving value, borrowed from hendrasd (http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=18937&FORUM_ID=8&CAT_ID=1&Topic_Title=Running+value+based+on+previous+row&Forum_Title=T%2DSQL)
Formula:
MV = (Previous MV) + .2 * (Sales - Previous MV)
which simplifies to:
MV = .8 * Previous MV + .2 * Sales
The first MV = Sales.
As far as I can tell, running values can either be coded via loops or tricks that can be used to avoid loops.
The R_Sum is fairly simple and can be coded w/o a loop:
```
update Table1

set R_Sum = (select sum(b.Sales) 

from Table1 b 

where b.nDay <= Table1.nDay) 
```
MV can also be calculated w/o a loop, but the math is too difficult for SQL Server - accuracy diminishes. Therefore a loop is best:
```
declare @c int

declare @cEnd int

select @cEnd = max(nDay) from table1

select @c = min(nDay) from table1



update table1

set MV = Sales where nDay = @c



WHILE @c < @cEnd

BEGIN

set @c = @c +1

update table1

set MV = 

  (select .8 * t.MV from table1 t 

   where t.nDay = table1.nDay - 1) 

         + .2 * Sales

where table1.nDay = @c

END
```
Does anyone know of another way to calculate running values in SQL Server? I get the feeling that I might be missing something
Data: Easy to spill, hard to clean up!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Search the forums for a thread named 'Help me beat EXCEL' or something like that. Must have been activ in April. Can't help you with the actual thread ID, for I am not at work right now. IIRC, there were several solutions for your problem.

HTH

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Greg M. Lucas Right there with Babe Points: 782 More actions · Answer 2

This is the article Frank mentions...

http://www.sqlservercentral.com/Forum/topic.asp?TOPIC_ID=11095&FORUM_ID=8&CAT_ID=1

. . Greg

Greg M Lucas MCDBA, MCSE

Greg M Lucas
"Your mind is like a parachute, it has to be open to work" - Frank Zappa

Jorginho Old Hand Points: 397 More actions · Answer 3

You can use a powerful but little-known technique, namely a running update with variables:

DECLARE @Sales DECIMAL(10,2), @MV DECIMAL(10,2)

SELECT @Sales = 0, @MV = 0

UPDATE Table1

SET @MV = MV = CASE WHEN nDay = 1 THEN Sales ELSE 0.8*@MV + 0.2*Sales END,

@Sales = Sales

You do need a clustered index on the field that determines the order (nDay in this case) to ensure that the rows are updated in the right order. This method is MUCH faster than using a cursor or a while loop!

Jorg Jansen

Manager Database Development

Infostradasports.com

Nieuwegein

The Netherlands

Jorg Jansen
Manager Database Development
Infostradasports.com
Nieuwegein
The Netherlands

cmore SSCrazy Points: 2402 More actions · Answer 4

quote:
You can use a powerful but little-known technique, namely a running update with variables:

I agree - in many cases it has been the optimal solution for me when dealing with spreadsheet-like calculations.

noeld SSC Guru Points: 96590 More actions · Answer 5

I would calculate the Value at INSERT time so that you don't have to do it after the fact!!

* Noel

stubob SSCommitted Points: 1839 More actions · Answer 6

Wow!! that's awesome There should be prizes for great solutions like that.

I learned a few things:

1. I didn't know a double assignment would work.

2. I didn't know a clustered index would ensure the order in an update - is this behavior documented somewhere or just assumed?

3. Asking questions on sqlservercentral pays off!

Your solution is probably much faster, and is certainly more straightfoward, concise & easy to read. Look at it after we get rid of the unnecessary @sales & case:

 

DECLARE @MV real

SELECT @MV = Sales from table1 where nDay = 1



UPDATE Table1

SET @MV = MV = 0.8*@MV + 0.2*Sales

Remember hendrasd's problem also included separate items in an Item column. This could probably be solved via a loop for each item, or maybe better, a table variable to store running update values for each item.

Thanks again, I really learned something. Where are you in the Netherlands? I met my wife in Utrecht.

Data: Easy to spill, hard to clean up!