Calculating a new value based on the value of a prior row

Hello,

I have a table set up similar to the on below:

Date| Starting Balance | Quantity Received | Quantity Used | New Balance

The new balance is the starting balance for the next row.

So for Row 1... The formula is

Starting Balance + Quantity Received - Quantity Used = New Balance

For Row 2 to End for each part it is

Previous Lines New Balance + Quantity Received - Quantity Used = New Balance

I think you can do this by adding a rank row and some Case Statements but I am getting my self wrapped around the Axle.  

Any help will be appreciated.

Mike
Mike DeFeliceAsked:
Who is Participating?
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
Assuming that this is SQL Server 2012 and above, you can use the ORDER BY clause of windowing/ranking functions. So, you are right on target about the tentative solution.

I wrote a post about using ranking functions to do a running total. You can leverage the same solution by modifying the formula to refer to your fields.

My post is available for your reference at: https://nakulvachhrajani.com/2014/04/03/0330-sql-server-2012-running-total-problem-and-windowing-functions/
1
 
Mike DeFeliceAuthor Commented:
Thank you!  This worked great!
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Good  to know that my post helped in resolving your issue! Request you to please close this question in case there are no follow-up queries.
0
 
Mike DeFeliceAuthor Commented:
I assigned you as the best solution.  Is there anything else I have to do to close it?
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Hello!

You may need to click on "Next"  and then close the question (http://support.experts-exchange.com/customer/en/portal/articles/2527982-how-do-i-close-my-question-).

Thanks! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.