1

enter image description here

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

2 Answers 2

2

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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.