SQL Server Running Balance

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?
LVL 39
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vaibhav GoelMSBI , SQL ConsultantCommented:
Hello gdemaria

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

Mark WillsTopic AdvisorCommented:
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 AdvisorCommented:
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

-- 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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

gdemariaAuthor Commented:
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.
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 AdvisorCommented:
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"
gdemariaAuthor Commented:
Thank you Mark  :)  

I can use a cursor if you don't have time..
Mark WillsTopic AdvisorCommented:
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 ?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gdemariaAuthor Commented:
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 AdvisorCommented:
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 :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.