SQL Server : Calculate amortization

Question

目前我正在努力解决以下问题:
我有两个表(契约(Contract)表和摊销表)。

契约(Contract):未清金额的契约(Contract)信息
摊销:付款日期和摊销金额

我想要的结果是一张包含契约(Contract)和递减的表格未清金额。

;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

目前我得到这个结果 - 这当然是错误的，因为只计算了 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

我想要的结果是:

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

有什么简单的想法可以轻松解决这个问题吗？

Rextester:http://rextester.com/SBLT77863

提前致谢!

Answer 1

我认为你只需要一个累计总和:

SELECT con.ID,
       con.outstanding,
       amo.amortization,
       amo.amo_date,
       (con.outstanding -
        sum(amo.amortization) over (partition by amo.contractId 
                                    order by amo.amo_date)
       ) as new_outstanding
FROM contract con INNER JOIN
     amort amo
     ON amo.contractID = con.ID;