SQL Server: Depreciation Calculation
I am currently afraid to solve the following problem:
I have two tables (contract and depreciation).
Contract: information about the contract with the amount outstanding
Depreciation: due date and amortization amount
My desired result is a table with a contract and a dropdown amount reduction.
;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
I am currently getting this result - this is of course wrong, since only one depreciation is calculated for 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 output:
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 a simple way?
Rextester: http://rextester.com/SBLT77863
Thanks in advance!
+3
source to share
1 answer
I guess you just want the cumulative sum:
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;
+5
source to share