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

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);
``````

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

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
``````