Using prior line record in next line record SQL

Question

[The data set above is what I have created.

What I'd like to do is loop the last column (New_UPB) and have that be the first column in the next line of records and have the data set continue until the UPB reaches 0.]1

So that the outcome is this:

I have all of the fields already in my database as a temp table, I just need to figure out how to loop that until the installments complete but not sure how to work that.

This is what my query looks like so far:

SELECT
AMS.Loan,
AMS.Payment#,
AMS.Due_Date,
AMS.UPB,
AMS.Int_Rate,
AMS.Total_PI,
AMS.Monthly_Int_Amt,
AMS.Monthly_Prin_Amt,
AMS.New_UPB

FROM    #AmSchedule AMS

WHERE   1=1

Answer 1

Since you are using SQL Server, you can use a Recursive Common Table Expression.

A Recursive CTE, is composed of two complementary queries unioned together. The first query is the anchor which sets up the initial conditions for the recursion or looping, while the second query does the recursion by doing a self referential select. That is it references the Recursive CTE in its from clause:

  -- vvvvvvvvvvvv this is the Recursive CTEs name
with RecursiveCTE(Loan, Payment#, Due_Date, UPB, Int_Rate, Total_PI,
                  Monthly_Int_Amt, Monthly_Prin_Amt, New_UPB)
  as (
  -- The Anchor Query
  SELECT AMS.Loan,
         AMS.Payment#,
         AMS.Due_Date,
         AMS.UPB,
         AMS.Int_Rate,
         AMS.Total_PI,
         AMS.Monthly_Int_Amt,
         AMS.Monthly_Prin_Amt,
         AMS.New_UPB
    FROM #AmSchedule AMS

  UNION ALL

  -- The Recursive Part
  SELECT Prior.Loan,
         Prior.Payment# + 1,     -- Increment Pay#
         dateadd(mm, 1, Prior.Due_Date), -- Increment Due Date
         Prior.new_UPB,          -- <-- New_UPB from last iteration
         Prior.Int_Rate,
         Prior.Total_PI,
         Prior.Monthly_Int_Amt,  -- <-- Put your
         Prior.Monthly_Prin_Amt, -- <-- calculations
         Prior.New_UPB           -- <-- here
    FROM RecursiveCTE Prior
      -- ^^^^^^^^^^^^ this is what makes it recursive
)
-- Output the results
select * from RecursiveCTE