1

I have a table like below: enter image description here

  1. Need to calculate interest for next month 100*(4.29/100).
  2. Row+1:Capital_Balance = Row:Capital_Balance_Calculated - Calculated interest from above step.

Resultant table is like below: [![enter image description here][2]][2]

For sample data:

CREATE TABLE #Test (
     Account_Number int
    ,Interest_Rate float
    ,LastDateOfMonth Date
    ,Capital_Balance_Calculated Float
    ,Interest float
    ,Payment float

);

INSERT INTO #Test VALUES (12345,4.2,'2019-03-31',100,5,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-04-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-05-31',0,NULL,5);
INSERT INTO #Test VALUES (12345,4.2,'2019-06-30',0,NULL,7);
INSERT INTO #Test VALUES (12345,4.2,'2019-07-31',0,NULL,7);
INSERT INTO #Test VALUES (54321,5.1,'2019-03-31',200,6,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-04-30',0,NULL,6);
INSERT INTO #Test VALUES (54321,5.1,'2019-05-31',0,NULL,8);
INSERT INTO #Test VALUES (54321,5.1,'2019-06-30',0,NULL,10);
INSERT INTO #Test VALUES (54321,5.1,'2019-07-31',0,NULL,8);

Please take a look at the calculation: enter image description here

  • I tried using While loop and joined on the same table based on row_number but it got way too complicated. – Sunil Babu Vudimudi Apr 15 at 14:20
  • Please consider reading this advice – mustaccio Apr 15 at 15:29
  • Are Capital_Balance_Calculated and Interest amounts in the expected results correct ? One set has decreasing capital balance while the other has increasing capital balance. – armitage Apr 15 at 15:56
  • Hi Armitage, Yes the numbers are correct. Thats is because of the payments column. Th ecustomer is being charged the interest month on month. The customer is also paying some amount month on month(say direct debit ) – Sunil Babu Vudimudi Apr 15 at 16:08
  • it doesn't make sense that both customers are making payments, and one customer balance is decreasing while the other's is increasing. Can you provide the full formula that you use? – SQLRaptor Apr 15 at 16:13
1

Your sample data script has an error, it uses 4.2 and 5.1 instead of 4.29 and 1.54. This threw off the numbers so please take care next time to make sure you get it right... Also, after the edit the desired result is missing. It is unclear whether the payment is from the 'current' row or the previous one, and in either case, for the first customer the balance drops and then increases even though the payment is more than the interest... Please check that as well.

Here is my solution, and while there is still some variance from your original desired result which I believe is wrong, this should get you on the right path so you can adjust it to fit your needs. I chose to use ARN.Payment in the balance calculation in the recursive part of the CTE for the 'current row', change that to 'RI.Payment' if you want to use the 'previous row' payment. It is impossible to tell from your original table which one you meant.

The solution adds row numbers per account to allow the following recursive CTE to calculate the interest and payments row by row.

WITH Added_Row_Numbers AS
(
SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY Account_Number ORDER BY LastDateOfMonth) AS Rn
FROM   #Test
)
, Recursive_Interest AS
(
SELECT * 
FROM   Added_Row_Numbers 
WHERE  Rn =1
UNION ALL
SELECT ARN.Account_Number,
       ARN.Interest_Rate,
       ARN.LastDateOfMonth,
       RI.Capital_Balance_Calculated + (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) - RI.Payment
       AS Capital_Balance_Calculated,
       (ARN.Interest_Rate * RI.Capital_Balance_Calculated / 100) AS Interest_Paid,
       ARN.Payment,
       ARN.Rn
FROM   Recursive_Interest AS RI 
       INNER JOIN
       Added_Row_Numbers AS ARN
       ON ARN.Rn = RI.Rn + 1 
          AND 
          RI.Account_Number = ARN.Account_Number
)
SELECT RI.Account_Number,
       RI.Interest_Rate,
       RI.LastDateOfMonth,
       RI.Capital_Balance_Calculated,
       RI.Interest,
       RI.Payment
FROM   Recursive_Interest AS RI
ORDER BY Account_Number, LastDateOfMonth

Here is the DBFiddle script.

HTH

  • Thank you. I will take care of the decimals while asking next time. I did face an error:'Types don't match between the anchor and the recursive part'. So, I had to CAST. The code was perfect. It worked well to calculate the capital balance. – Sunil Babu Vudimudi Apr 16 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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