-- THIS IS MY TEST DATA
create  table #tmp (SalesId varchar(32), LastPayDate datetime, Amt int, 
rate int, EndingBalance int);

insert into #tmp values ('2331739','2011-01-01','300','2',null);
insert into #tmp values ('4131556','2010-12-01','800','5',null);

-- THIS IS THE QUERY USING RECURSIVE CTE
;with CTE1 as
(
   select  -- anchor part of the cte calculates endingbalance for rows in the table
      SalesId,
      LastPayDate, 
      Amt, 
      Rate,
      cast(Amt*Rate as int) as EndingBalance
   from #tmp 
   
   union all
   
   select -- recursive part calculates it for the subsequent month
      c1.SalesId,
      dateadd(month,1,c1.LastPayDate) as LastPayDate,
      c1.EndingBalance as Amt,
      c1.Rate as Rate,
      c1.EndingBalance * c1.Rate as EndingBalance
   from
      CTE1 c1
   where
      dateadd(month,2,c1.LastPayDate) < getdate()
)
select * from CTE1 order by SalesId, LastPayDate;

-- AND THEN CLEANING UP
if (object_id('tempdb..#tmp') is not null) drop table #tmp;

select data_type from INFORMATION_SCHEMA.columns where table_name = 'YourTableName' and
column_name = 'YourLastPayDateColumnName'

select min(cast(LastPayDate as datetime)) from YourTable

select SalesID, count(*) from YourTable group by SalesID having count(*) > 1

quote:

---

Originally posted by dz0001
Also one more information is that actual ending balance calculation is not only relied on Rate, but also on another calculation, for example,

SalesID LastPayDate Amt Rate InterestAmt EndingBalance

InterestAmt initially equals Amt*Int, then EndingBalance = Amt*Rate+InterestAmt.

So when it becomes beginning balance for next month, then InterestAmt will change too.

---

create  table #tmp (SalesId varchar(32), DueDate datetime, Amt float, InterestRate float,
InterestAmount float, EndingBalance float);

insert into #tmp values ('0021111133','2010-12-01','212229.57','7.26',null,null);
insert into #tmp values ('0021112032','2011-01-01',' 557921.87','2',null,null);
insert into #tmp values ('0021112031','2000-01-01',' 557921.87','2',null,null);

-- THIS IS THE QUERY USING RECURSIVE CTE
;with CTE1 as
(
   select  -- anchor part of the cte calculates endingbalance for rows in the table
      SalesId,
      DueDate, 
      Amt, 
      InterestRate,
      cast(Amt*InterestRate/1200.0 as float) as InterestAmount,
      cast(Amt-Amt*InterestRate/1200.0 as float) as EndingBalance
   from #tmp 
   
   union all
   
   select -- recursive part calculates it for the subsequent month
      c1.SalesId,
      dateadd(month,1,c1.DueDate) as DueDate,
      c1.EndingBalance as Amt,
      c1.InterestRate,
      cast(c1.EndingBalance*InterestRate/1200.0 as float) as InterestAmount,
      cast(c1.EndingBalance -  c1.EndingBalance*InterestRate/1200.0 as float) as EndingBalance
   from
      CTE1 c1
   where
      dateadd(month,2,c1.DueDate) < getdate()
)
select * from CTE1 order by SalesId, DueDate OPTION (MAXRECURSION 150);

-- AND THEN CLEANING UP
if (object_id('tempdb..#tmp') is not null) drop table #tmp;

-- THIS IS THE QUERY USING RECURSIVE CTE
;with CTE1 as
(
   select  -- anchor part of the cte calculates endingbalance for rows in the table
      SalesId,
      DueDate, 
      Amt, 
      InterestRate,
      cast(Amt*InterestRate/1200.0 as float) as InterestAmount,
      cast(Amt-Amt*InterestRate/1200.0 as float) as EndingBalance,
      1 as RecursionLevel
   from #tmp 
   
   union all
   
   select -- recursive part calculates it for the subsequent month
      c1.SalesId,
      dateadd(month,1,c1.DueDate) as DueDate,
      c1.EndingBalance as Amt,
      c1.InterestRate,
      cast(c1.EndingBalance*InterestRate/1200.0 as float) as InterestAmount,
      cast(c1.EndingBalance -  c1.EndingBalance*InterestRate/1200.0 as float) as EndingBalance,
      c1.RecursionLevel+1
   from
      CTE1 c1
   where
      dateadd(month,2,c1.DueDate) < getdate()
      and RecursionLevel < 149
)
select * from CTE1 where RecursionLevel > 148 order by SalesId, DueDate OPTION (MAXRECURSION 150);