3

First time posting but have been a reader for over 10 years. I have a need to show when a loan was paid off regardless of the rolling balance from refinancing/taking out additional loans.

In the other column I want to show pay down balance like 950.00, 850.00, 800.00, 720.00 until I get to zero balance for the LOAN_ID 215, After which I want to then apply remaining payments to LOAN_ID 431

I'm using SQL Server 2005/2008.

Here is my table:

CUST_ID LOAN_ID PMNT_ID PMNT_AMT  PMNT_DT   LOAN_AMT  LOAN_FUND_DT  PMNT_RUN_TOT  
1155      215    100    100.00   02/15/2015  1050.00   01/15/2015    100.00
1155      215    101    100.00   03/15/2015  1050.00   01/15/2015    200.00
1155      215    102     50.00   03/31/2015  1050.00   01/15/2015    250.00
1155      215    103     90.00   04/15/2015  1050.00   01/15/2015    340.00
1155      215    104    150.00   04/25/2015  1050.00   01/15/2015    490.00
1155      215    105    120.00   05/15/2015  1050.00   01/15/2015    610.00
1155      431    106    100.00   05/25/2015  2100.00   05/20/2015    710.00
1155      431    107    100.00   06/15/2015  2100.00   05/20/2015    810.00
1155      431    108    100.00   06/30/2015  2100.00   05/20/2015    910.00
1155      431    109    100.00   07/15/2015  2100.00   05/20/2015    1010.00
1155      431    110    100.00   08/15/2015  2100.00   05/20/2015    1110.00
1155      431    111    100.00   09/15/2015  2100.00   05/20/2015    1210.00
1155      431    112    100.00   09/30/2015  2100.00   05/20/2015    1310.00
1155      431    113    100.00   10/15/2015  2100.00   05/20/2015    1410.00

The first loan was paid off with PMNT_ID 110 and 60.00 was rolled over into the pay down of 2100.00 loan. Here is how the output should look

cust_id|loan_id | loan_amount | Loan Bal |paid_off_date

1155 | 215| 1050 | 0.00 |2015-08-15 1155 | 431| 2100 | 1800.00 |null

Any suggestions on how to show this in another column called "Bal_Pay_Down" ?

  • I would add another field and label it LOAN_PNMT_ID, use the loan_id that the pmt_amt is being applied to, instead of the loan_id of the loan. Then it'd be pretty easy to calculate a running total, use the loan_pmt_id and pmnt_amt to get your running balances. – Jason B. Jul 25 '16 at 20:43
  • In the other column I want to show pay down bal like 950.00, 850.00, 800.00, 720.00 until I get to 0 for the LOAN_ID 215, After which I want to then apply remaining payments to LOAN_ID 431 – CJack Jul 25 '16 at 20:48
1

To solve this problem, I did the following (+1 for the question BTW - it was an interesting challenge and I learnt a lot by going through it).

I used PostgreSQL to do this - don't have a SQL server instance running, but the principles should be the same. Also, it might be nice for your next question (in a decade's time?) to have DDL and DML :-)

Created a table:

CREATE TABLE payment (CUST_ID int, LOAN_ID int, PMNT_ID int, PMNT_AMT float,  PMNT_DT date,   LOAN_AMT float,  LOAN_FUND_DT date,  PMNT_RUN_TOT int);

Populated the table:

INSERT INTO payment VALUES (1155,      215,         100,         100.00,   '2015-02-15',           300.00,        '2015-01-15' ,       100.00);
INSERT INTO payment VALUES (1155,      215,         101,          50.00,   '2015-03-15',           300.00,        '2015-01-15' ,       150.00);
INSERT INTO payment VALUES (1155,      215,         102,          75.00,   '2015-04-15',           300.00,        '2015-01-15' ,       225.00);
INSERT INTO payment VALUES (1155,      215,         103,          75.00,   '2015-05-15',           300.00,        '2015-01-15' ,       300.00);

INSERT INTO payment VALUES (1156,      223,         104,          30.00,   '2015-02-15',           390.00,        '2015-01-15' ,        30.00);
INSERT INTO payment VALUES (1156,      223,         105,          30.00,   '2015-03-15',           390.00,        '2015-01-15' ,        60.00);
INSERT INTO payment VALUES (1156,      223,         106,         300.00,   '2015-04-15',           390.00,        '2015-01-15' ,       360.00);
INSERT INTO payment VALUES (1156,      223,         107,          30.00,   '2015-07-27',           390.00,        '2015-01-15' ,       390.00);

And then I ran the following query (don't worry about the overkill/repetition on the PARTITION BYs - I was trying to get to grips with Window functions).

WITH tab1 AS
(
  SELECT n.cust_id, n.loan_id, n.pmnt_id, pmnt_dt, pmnt_amt,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.cust_id, n.loan_id) AS loans,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.pmnt_id) AS payments,
  SUM(n.pmnt_amt) OVER () AS grand_total,
  SUM(n.pmnt_amt) OVER (partition by loan_id ORDER BY pmnt_id) AS payment_per_loan,
  SUM(n.pmnt_amt) OVER (ORDER BY loan_id) AS loans_cumulative,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.loan_id) loan_amount,
  (SUM(n.pmnt_amt) OVER (PARTITION BY n.cust_id, n.loan_id)) - 
  (SUM(n.pmnt_amt) OVER (partition by loan_id ORDER BY pmnt_id)) AS remainder_per_loan
  FROM payment AS n
  ORDER BY cust_id, n.loan_id, n.pmnt_id, n.pmnt_dt
)
SELECT cust_id, loan_id, loan_amount, pmnt_dt AS paid_off_date FROM tab1
WHERE remainder_per_loan = 0

And the result is (with the limited subset of data that I used):

cust_id|loan_id|loan_amount|paid_off_date
----------------------------------------------------
  1155|     215|        300|     2015-05-15
  1156|     223|        390|     2015-07-27
  • Wont work the scope of my question was changed.. A loan is not paid off before another is taken. – CJack Jul 26 '16 at 11:34
  • Don't see that as a problem! Just look at my sample data - there's an overl in the loan periods. Can you put up sample data that illustrates your point - DML format please. – Vérace Jul 26 '16 at 13:22
  • Yea you are correct :) – CJack Jul 27 '16 at 1:56
  • It seems helpful but I am using SQL 2005 and it seems the order by part is not recognized. SMH. – CJack Jul 27 '16 at 5:47
  • I saw that you were on SQL Server 2005 and checked. Apparently CTEs and window functions are (or should be) supported. What error do you get? – Vérace Jul 27 '16 at 7:13

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.