Vryjdfkk

I have a table and I am trying to apply payments to the loan amounts. Payments must be applied to initial loan until it is zero before going to next loan.

First I must say cursors are not my strong point but I gave it a try. I cannot get the cursor to apply the pmnt_amt from the DEAL_FP_CALC_BEG_BAL_AMT until it gets to zero and apply any remaining payments to the next DEAL_FP_CALC_BEG_BAL_AMT.

For example a DEAL_FP_CALC_BEG_BAL_AMT of 5000 will be paid down like this:

5000.00- 1235.3 = 3764.7

3764.7 - 1122.00 = 2642.7

2642.7 - 1035.3 = 1607.4

1607.4 - 1005.4 = 602.00

602.4 -1005.2 = (403.2) Loan paid off

(403.2) + 7500 - 1235.3(next payment) = 5861.5 until this is paid

Table is

FP_MERCH_ID  PMNT_SEQ_ID  PMNT_AMT  PMNT_DT   DEAL_FP_BAL_AMT  FUND_DT

2359         1122         1235.3    10/1/15   5000             9/1/15

2359         1123         1122      10/12/15  5000             9/1/15

2359         1124         1035.3    10/19/15  5000             9/1/15

2359         1125         1005.4    10/24/15  5000             9/1/15

2359         1126         1105.2    10/29/15  5000             9/1/15

2359         1127         1235.3    11/3/15   7500             10/31/15

2359         1128         1122      11/8/15   7500             10/31/15

2359         1129         1035.3    11/13/15  7500             10/31/15

2359         1130         1005.4    11/18/15  7500             10/31/15

2359         1131         1105.2    11/23/15  7500             10/31/15

Here is my code below.
DEAL_FP_CALC_BEG_BAL_AMT is loan amount.

--DECLARE @COUNTER INT

DECLARE @PAYMENT INT

DECLARE @BALANCE INT

--DECLARE @MAXCOUNT INT

DECLARE @RESULTS INT

DECLARE @FP_CALC_BEG_BAL_AMT INT

SET @FP_CALC_BEG_BAL_AMT = (SELECT MAX(DEAL_FP_CALC_BEG_BAL_AMT)

                        From LOOPDATA_NEW where FP_MERCH_ID = 2359

                      )



DECLARE VINTAGE CURSOR 

    FOR SELECT PMNT_AMT From LOOPDATA_NEW Where FP_MERCH_ID = 2359  

                        Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT





OPEN VINTAGE 



    FETCH NEXT FROM VINTAGE INTO @PAYMENT



    WHILE @@FETCH_STATUS = 0 



    BEGIN 



    SET @BALANCE = @FP_CALC_BEG_BAL_AMT - @PAYMENT  

        /* begin

        set @RESULTS = @BALANCE - @PAYMENT

        end */

    PRINT @BALANCE



FETCH NEXT FROM VINTAGE INTO @PAYMENT



END

CLOSE VINTAGE

DEALLOCATE VINTAGE

You never actually reduce your balance here. I think you mean to do this:

SET @BALANCE = @FP_CALC_BEG_BAL_AMT;

OPEN VINTAGE;

FETCH NEXT FROM VINTAGE INTO @PAYMENT;

WHILE @@FETCH_STATUS = 0

BEGIN

  SET @BALANCE = @BALANCE - @PAYMENT;

FETCH NEXT FROM VINTAGE INTO @PAYMENT;

END

CLOSE VINTAGE;

DEALLOCATE VINTAGE;

In this example, you set your starting balance to the initial loan amount. Then on each iteration of the cursor you reduce the balance by the payment amount.

I'm not sure why you chose to use a cursor, I am sure you have your reasons, but would the below not achieve the desired result without using one?

DECLARE @TOTAL_PAYMENTS INT;

    SET @TOTAL_PAYMENTS = (SELECT SUM(PMNT_AMT) 

                             From LOOPDATA_NEW 

                            Where FP_MERCH_ID = 2359  

                         Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT);

    SET @BALANCE = @FP_CALC_BEG_BAL_AMT - @TOTAL_PAYMENTS;

Also, in your code you are casting your balance and payments to INT, this loses the decimal precision which could cause you to lose money if this were to be a real-world example. Remember to use an accurate data type such as DECIMAL so that you capture these values correctly.

EDIT BASED ON COMMENT

The reason you only every return the largest loan is because of this line in your code:

DECLARE @FP_CALC_BEG_BAL_AMT INT

    SET @FP_CALC_BEG_BAL_AMT = (SELECT MAX(DEAL_FP_CALC_BEG_BAL_AMT)

                                  From LOOPDATA_NEW 

                                 where FP_MERCH_ID = 2359);

You only ever fetch the largest loan here. If you want to loop through each loan and then each payment you can do this:

DECLARE @loan DECIMAL(9,2) = 0;

DECLARE @balance DECIMAL(9,2) = 0;

DECLARE @payment DECIMAL(9,2) = 0;



DECLARE loans CURSOR 

    FOR (SELECT DISTINCT DEAL_FP_CALC_BEG_BAL_AMT

           From LOOPDATA_NEW 

          where FP_MERCH_ID = 2359);

 OPEN loans;

FETCH NEXT FROM loans INTO @loan;

WHILE @@FETCH_STATUS = 0

BEGIN

  SET @balance = @balance + @loan;



  DECLARE payments CURSOR 

      FOR (SELECT PMNT_AMT 

            From LOOPDATA_NEW 

           Where FP_MERCH_ID = 2359

        Group by FP_MERCH_ID, PMNT_SEQ_ID, PMNT_AMT);



   OPEN payments;

  FETCH NEXT FROM payments INTO @payment;

  WHILE @@FETCH_STATUS = 0 AND @balance > 0

  BEGIN

    SET @balance = @balance - @payment;

  FETCH NEXT FROM payments INTO @payment;

  END

  CLOSE payments;

  DEALLOCATE payments;

FETCH NEXT FROM loans INTO @loan;

END

CLOSE loans;

DEALLOCATE loans;

PRINT @balance;

Aaron Bertrand gives a fantastic answer on the different methods you can use to calculate running totals for this question. I highly recommend checking it out as I feel it will help you in the long run.