SQL Server: Calculate amortization

Question

Currently i'm struggling to solve following problem:
I have two tables (contract & amortization).

Contract: contract informations with an outstanding amount
Amortization: date of payment & amortization amount

My desired result is a table with the contract and the decreasing oustanding amount.

;WITH CTE AS
(
        SELECT con.ID
              ,con.outstanding
              ,amo.amortization
              ,amo.amo_date
              ,ROW_NUMBER() OVER ( PARTITION BY con.ID
                                       ORDER BY amo.amo_date asc
                                  ) as rn    
          FROM contract con
    INNER JOIN amort amo
            ON amo.contractID = con.ID
)
SELECT ID
      ,outstanding
      ,outstanding - amortization as new_outstanding
      ,amo_date
  FROM CTE

Currently i am getting this result - which is of course wrong, as just one amortization is calculated for the new_outstanding:

ID  outstanding     new_outstanding     amo_date
1   100000          90000               01.08.2017 00:00:00
1   100000          80000               01.09.2017 00:00:00
1   100000          50000               01.10.2017 00:00:00
1   100000          90000               01.11.2017 00:00:00
1   100000          90000               01.12.2017 00:00:00

My desired result would be:

ID  outstanding     new_outstanding     amo_date
1   100000          90000               01.08.2017 00:00:00
1   100000          70000               01.09.2017 00:00:00
1   100000          20000               01.10.2017 00:00:00
1   100000          10000               01.11.2017 00:00:00
1   100000          0                   01.12.2017 00:00:00

Any simple idea to solve this in an easy way?

Rextester: http://rextester.com/SBLT77863

Thanks in advance!

SQL Server: Calculate amortization

1 Answers