tsql - Calculating Running Balances -

i struggling how calculate running balance dynamic. here's have.

• a range of periods intersection between total duration , each month in duration.

• a rate applied @ each intersection.

• an initial amount

• a need calculate running balance based on initial amount less rate applied in period.

for example, have project worth $2,500,000 8 months long. rates each interval 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 have $202,500 (8.10% × $2.5 million), period 2, have $322,500 (14.04% × $2,297,500 ($2.5 - $202,500)), period 3, have $530,000 (26.8% × $1,974,999 ($2.5 - sum of first 2 periods ($525,000)). @ end of period 8, balance $0 , earned amount = $2.5 million.

can use runningtotal = sum(monthlyamts) on (order xx rows unbounded preceding), order period? or candidate cursor?

thanks in advance!

it depends on table structure. sounds should use function. if years use table valued function, if interested on result use scalar function. or better way recursive cte.