- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

I am struggling with how to calculate a running balance that is dynamic. Here's what I have. 1. A range of periods with an intersection between total duration and each month in the duration. 2. A rate that is applied at each intersection. 3. An initial amount 4. A need to calculate a running balance based on the initial amount less the rate applied in that period. For example, I have a project worth $2,500,000 that is 8 months long. The rates for each interval are as follows: 1. 8.10% 2. 14.04% 3. 26.8% 4. 29.1% 5. 33.4% 6. 30.4% 7. 47.4% 8. 100% For period 1 I have $202,500 (8.10% × $2.5 million), For period 2, I have $322,500 (14.04% × $2,297,500 ($2.5 - $202,500)), for period 3, I have $530,000 (26.8% × $1,974,999 ($2.5 - sum of first two periods ($525,000)). At the end of period 8, my balance is $0 and my earned amount = $2.5 million. Can I use something like RunningTotal = Sum(MonthlyAmts) OVER (ORDER BY XX ROWS UNBOUNDED PRECEDING), ORDER BY Period? Or is this a candidate for a cursor? Thanks in advance!

t-sqlsql-server-2014running-total
Comment

@tlenzmeier From the first glance, this is NOT a candidate for a cursor, but could you please provide a small set of sample data (something like a script which creates, say, temp table(s) or table variable(s) and populates them with sample data)? This way, it is much easier to come up with the solution. Thank you.

CREATE TABLE #Test([Period] INT, [Rate] Decimal(14,6)) INSERT INTO #Test([Period], Rate) VALUES (1, 0.081) ,(2, 0.14037) ,(3, 0.268354) ,(4, 0.290657) ,(5, 0.334146) ,(6, 0.304029) ,(7, 0.473684) ,(8, 1) DECLARE @OrigAmt MONEY SET @OrigAmt = 2500000 SELECT Period ,Rate ,RunningTotal = [Rate] * (?? --OVER (ORDER BY [Period] ROWS UNBOUNDED PRECEDING)?? FROM #Test

**18** People are following this question.

Copyright 2019 Redgate Software.
Privacy Policy