How can I calculate the loan balance based on following data set

Question

There are two loans 12345, 54321. I want to arrive at 'New Column' which is calculated column based on Balance and Transaction_amount. Also, need to calculate till maturity date.

CREATE TABLE #Test (
    Account_Number int,
    Maturity_Date Date,
    Interest_Charged_Date Date,
    Transaction_Amount float,
    Balance Float
);

INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-05-12',0,100);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-06-12',28.77,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-07-12',-28.83,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-08-12',28.77,0);
INSERT INTO #Test VALUES (12345, '2016-09-12', '2016-09-12',28.87,0);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-06-28',0,250);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-07-28',-17.25,0);
INSERT INTO #Test VALUES (12345, '2016-08-28', '2016-08-28',17.18,0);

Answer 1

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

Answer 2

Someone having same requirement in Lower version of Sql Server,

SELECT Account_Number
,Maturity_Date
,Interest_Charged_Date
,Transaction_Amount
,balance
,ca.Tamt + ca.Bal AS [New Columns]
FROM #Test T
CROSS APPLY (
SELECT sum(T1.Transaction_Amount) Tamt
,sum(t1.balance) Bal
FROM #Test T1
WHERE t.Account_Number = t1.Account_Number
AND t.Interest_Charged_Date >= t1.Interest_Charged_Date
) ca