Sql-server – How to make this Cursor work better

cursorssql serversql-server-2008t-sql

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

Best Answer

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.

Related Question