SQL Server Running Balance

gdemaria
gdemaria used Ask the Experts™
on
I think I need a cursor for this, but just wanted to check to see if any new features would make this possible.

I have a table with a date and a value, the value ends with the rest null.
I'd like to fetch the 1000 initial value and do some math on it, then save that result into the next record (1/2) and then take that value and do some math and save it to the next record and so on..

Update myTable
   set  value =  (previousTotal + column2 * aFunction( rand() ) )

Date ...   Value
1/1         1000
1/2         NULL
1/3         NULL
1/4         NULL

Cursor required?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vaibhav GoelMSBI , SQL Consultant

Commented:
Hello gdemaria

Please provide expected output. We also need information about aFunction?

Vaibhav
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
What version of SQL Server

If after 2008 then you can use window functions in aggregates :

sum(Column) over (partition by .... Order by ... ) as running_total

Otherwise, you can link back on itself to get sum(Values) < date

Shouldnt need cursor, but, might be some kind of recursion in the worst case
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
OK, still need clarification, but in the meantime....

A couple of examples of the types of ways which it might be accommodated :

And please note they are just purely examples, wont be able to provide a real solution until you clarify some of the requests for information above...

select    t.Entity_Field
        , t.Date_Field
        , t.Numeric_Value
        , sum(t.Numeric_Value) over (partition by t.Entity_Field order by t.Date_Field) as running_total
from my_table T
order by 1,2,3
Go

-- Next we do a more traditional (?) link back with a unique row_number identifier

;with my_running_totals as
(select *, row_number() over (order by Entity_Field,Date_Field ) as rn
 from my_table)
select t.Entity_Field, t.Date_Field, t.Numeric_Value, sum(r.Numeric_Value) as running_total
from my_running_totals t
inner join my_running_totals r on r.Entity_Field = t.Entity_Field and r.rn <= t.rn
group by t.Entity_Field, t.Date_Field, t.Numeric_Value
order by 1,2,3

Open in new window

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Thank you for the responses!
I have SQL Server 2012 Web Edition.

aFunction ()  is a placeholder for a function that we wrote, it returns a value

The hard part, the way I see it, is that the next value must be based on the previous value, which has not been determned until the script is run.

As an example.
       RunningValue
1.   100
2     NULL
3     NULL
4     NULL

start with 100
calculate        =  100 + constant + ( 1.5 * aFunction(rand()))   say the result is 112.
so #2 becomes 112, now that it is populated, calculate #3                  
calculate        =  112+ constant + ( 1.5 * aFunction(rand()))   say the result is 130
so #3 becomes 130, now we calculate #4
calculate        =  130+ constant + ( 1.5 * aFunction(rand()))   say the result is 124  (yes it can drop)
so #3 becomes 124.... continue...

Thanks for any additional insights!
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Hmmm... might be right about a cursor. Seems to be very much RBAR (row by agonising row)

A recursive CTE is an option

But dont mind the challenge :) In the voice of Arnie "I'll be back"
Thank you Mark  :)  

I can use a cursor if you don't have time..
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
Tried a few different ways, and keep coming back to cursor...

So, to answer your question... YES cursor required - easiest to code and most straightforward.

Do you need a hand with the cursor ?
Thanks Mark- I really appreciate your efforts and advice.  All set with Cursor, just trying to avoid it / learn something new if it existed
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
A pleasure, and thank you for the challenge.

Truth be known, cursors are very hard to beat in the right hands for RBAR solutions.

So, wouldn't necessarily cave into a thought process that cursors are evil. Even MS have been known to use cursors :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial