You can use SUM()
function with OVER
clause (see here for more details)
Key point here is the window frame:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
This is saying to include into calculation , the rows from start (UNBOUNDED PRECEDING
) till the current row.
need to calculate till maturity date
for this you can add a Case
expression
;with cteSource AS
(
SELECT 12345 AS Account_Number ,'12/09/2016' as Maturity_Date, '12/05/2016' as Interest_Charged_Date,0 as Transaction_Amount , 100 As Balance UNION ALL
SELECT 12345 ,'12/09/2016' , '12/06/2016' ,28.77 , 0 UNION ALL
SELECT 12345 ,'12/09/2016' , '12/07/2016' ,-28.83 , 0 UNION ALL
SELECT 12345 ,'12/09/2016' , '12/08/2016' ,28.77 , 0 UNION ALL
SELECT 12345 ,'12/09/2016' , '12/09/2016' ,28.77 , 0 UNION ALL
SELECT 54321 ,'28/08/2016' , '28/06/2016' ,0 , 250 UNION ALL
SELECT 54321 ,'28/08/2016' , '28/07/2016' ,-17.25 , 0 UNION ALL
SELECT 54321 ,'28/08/2016' , '28/08/2016' ,17.18 , 0
)
SELECT
*
,SUM(case when Interest_Charged_Date < = Maturity_Date then Balance + Transaction_Amount else 0 end) OVER (PARTITION BY Account_Number
ORDER BY Interest_Charged_Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NEW_Column
,SUM(Balance + Transaction_Amount) OVER (PARTITION BY Account_Number
ORDER BY Interest_Charged_Date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS NEW_Column2
FROM
cteSource;
output :
Account_Number Maturity_Date Interest_Charged_Date Transaction_Amount Balance NEW_Column NEW_Column2
12345 12/09/2016 12/05/2016 0.00 100 100.00 100.00
12345 12/09/2016 12/06/2016 28.77 0 128.77 128.77
12345 12/09/2016 12/07/2016 -28.83 0 99.94 99.94
12345 12/09/2016 12/08/2016 28.77 0 128.71 128.71
12345 12/09/2016 12/09/2016 28.77 0 157.48 157.48
54321 28/08/2016 28/06/2016 0.00 250 250.00 250.00
54321 28/08/2016 28/07/2016 -17.25 0 232.75 232.75
54321 28/08/2016 28/08/2016 17.18 0 249.93 249.93
dbfiddle here