question

tlenzmeier avatar image
tlenzmeier asked

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!
t-sqlsql-server-2014running-total
2 comments
10 |1200

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 ·

1 Answer

·
tlenzmeier avatar image
tlenzmeier answered
I found the answer! 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; WITH CTE AS ( SELECT [Period], Amt = CAST([Rate] * @OrigAmt AS MONEY), Balance = CAST(@OrigAmt - (@OrigAmt * [Rate]) AS MONEY) FROM #Test WHERE [Period] = 1 UNION ALL SELECT b.Period, Amt = CAST(b.Rate * CTE.Balance AS MONEY), Balance = CAST(CTE.Balance - (CTE.Balance * b.Rate) AS MONEY) FROM CTE INNER JOIN #Test b ON b.Period = CTE.Period + 1 ) SELECT Period, Amt , Balance FROM CTE ORDER BY Period OPTION (MAXRECURSION 10)
10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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