# question

## Calculating Running Balance

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!

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@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.
0 Likes 0 ·
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
0 Likes 0 · 