update a set
   rate = b.rate
from
   TableA a
   cross apply 
   (
      select top 1 
         rate 
      from 
         TableB b 
      where 
         b.ratestartdate <= a.duedate 
         and b.sid = a.sid
         and b.cat = a.cat
      order by 
         ratestartdate desc 
   )b

-- THIS PART IS CREATING MY TEST TABLES AND DATA
CREATE TABLE TA(SID INT, DueDate DATETIME, Amount DECIMAL(19,2));
CREATE TABLE TB(SID INT, RateStartDate DATETIME, Rate DECIMAL(9,5));

INSERT INTO TA VALUES (577,'20101101',563500);
INSERT INTO TA VALUES (578,'20090101',563500);

INSERT INTO TB VALUES (577,'20100301',0.001);
INSERT INTO TB VALUES (577,'20101001',0.002);
INSERT INTO TB VALUES (577,'20110101',0.003);
INSERT INTO TB VALUES (577,'20110301',0.004);

INSERT INTO TB VALUES (578,'20090101',0.004);
INSERT INTO TB VALUES (578,'20090901',0.003);
INSERT INTO TB VALUES (578,'20100901',0.005);

-- PART 1
-- CONSTRUCTING A RATE TABLE THAT HAS RATES FOR ALL MONTHS.
CREATE TABLE #TC
(
   SID INT NOT NULL, 
   DueDate DATETIME NOT NULL, 
   Amount DECIMAL(19,2) NULL,
   Rate DECIMAL(9,5) NOT NULL
   PRIMARY KEY CLUSTERED (SID, DueDate)
);
WITH CalendarCTE AS 
(
   SELECT SID, DueDate, Amount FROM TA
   
   UNION ALL
   
   SELECT c.SID,DATEADD(mm,1,c.DueDate), null
   FROM CalendarCTE c
   WHERE DATEADD(mm,2,c.DueDate) < GETDATE()
)
INSERT INTO #TC
SELECT 
   c.SID,
   c.DueDate,
   Amount,
   b.Rate
FROM
   CalendarCTE c
   CROSS APPLY
   (
      SELECT TOP 1 Rate 
      FROM TB b
      WHERE b.RateStartDate <= c.DueDate
         AND c.SID = b.[SID]
      ORDER BY b.RateStartDate DESC
   ) b;

-- PART 2
-- CUMULATIVE AMOUNTS
-- See the two ??? below. I calculated the balances differently than you
-- described. May be that needs to be changed?

WITH CTE AS
(
   SELECT *,
      cast(Amount*Rate as decimal(19,5)) AS Interest -- ????
   FROM #TC 
   WHERE Amount IS NOT NULL
   
   UNION ALL
   
   SELECT
      t.SID,
      t.DueDate,
      cast(c.Amount+c.Interest AS DECIMAL(19,2)),
      t.Rate,
      CAST((c.Amount+c.Interest)*t.Rate AS DECIMAL(19,5)) -- ???
   FROM
      #TC t
      INNER JOIN CTE c ON t.DueDate = DATEADD(mm,1,c.DueDate)
         AND t.SID = c.SID
)
SELECT * FROM CTE ORDER BY SID, Duedate;

-- CLEANUP   
DROP TABLE #TC;

-- PART 1
-- CONSTRUCTING A RATE TABLE THAT HAS RATES FOR ALL MONTHS.
CREATE TABLE #TC
(
   SID INT NOT NULL, 
   DueDate DATETIME NOT NULL, 
   Amount DECIMAL(19,2) NULL,
   Rate DECIMAL(9,5) NOT NULL,
   Charge DECIMAL(9,5) NOT NULL,
   PRIMARY KEY CLUSTERED (SID, DueDate)
);
WITH CalendarCTE AS 
(
   SELECT SID, DueDate, Amount FROM TA
   
   UNION ALL
   
   SELECT c.SID,DATEADD(mm,1,c.DueDate), null
   FROM CalendarCTE c
   WHERE DATEADD(mm,2,c.DueDate) < GETDATE()
)
INSERT INTO #TC
SELECT 
   c.SID,
   c.DueDate,
   Amount,
   b.Rate,
   t.Charge
FROM
   CalendarCTE c
   CROSS APPLY
   (
      SELECT TOP 1 Rate 
      FROM TB b
      WHERE b.RateStartDate <= c.DueDate
         AND c.SID = b.[SID]
      ORDER BY b.RateStartDate DESC
   ) b
   CROSS APPLY
   (
      SELECT TOP 1 Charge 
      FROM TC t
      WHERE t.ChargestartDate <= c.DueDate
         AND c.SID = t.[SID]
      ORDER BY t.ChargestartDate DESC
   	)t;

-- PART 2
-- CUMULATIVE AMOUNTS
-- See the two ??? below. I calculated the balances differently than you
-- described. May be that needs to be changed?


WITH CTE AS
(
   SELECT *,
      cast(Amount*Rate as decimal(19,5)) AS ScheduleRate,
      cast(Charge-Amount*Rate as decimal(19,5)) AS ScheduleCharge,
      CAST(Amount - (Charge-Amount*Rate) AS DECIMAL(19,5)) AS EndingBalance
      
   FROM #TC 
   WHERE Amount IS NOT NULL
   
   UNION ALL
   
   SELECT
      t.SID,
      t.DueDate,
      cast(c.EndingBalance AS DECIMAL(19,2)),
      t.Rate,
      t.Charge,
      cast(c.EndingBalance*t.Rate as decimal(19,5)) AS ScheduleRate,
      CAST(t.Charge-c.EndingBalance*t.Rate as decimal(19,5)) AS ScheduleCharge,
      CAST(c.EndingBalance-(t.Charge-c.EndingBalance*t.Rate) AS DECIMAL(19,5)) AS EndingBalance
   FROM
      #TC t
      INNER JOIN CTE c ON t.DueDate = DATEADD(mm,1,c.DueDate)
         AND t.SID = c.SID
)
SELECT * FROM CTE ORDER BY SID, Duedate;

-- CLEANUP   
DROP TABLE #TC;

quote:

---

Originally posted by sunitabeck

I had missed that you had added two columns CAT and CATID in table TA. So everything you did up to the final select seems correct. But in the final select, how should the CatRate be taken into account? Currently the Charge, ScheduleRate, ScheduleCharge and EndingBalance are calculated without using the CatRate at all. Can you describe or post the formula you want to use to calculate these when you take into account the CatRate?

---

quote:

---

Originally posted by sunitabeck

Would that then reduce the Amount for the next month? What I am looking for is something like this that you had posted earlier:

ScheduleRate = Amount*Rate
ScheduleCharge = Charge - ScheduleRate
EndingBalance = Amount - ScheduleCharge
Previous EndingBalance becomes Amount for next period

Would DisAmt change any of the formulas above?

---

INSERT INTO TD VALUES (411,001,'20090701',0.06);
INSERT INTO TD VALUES (411,001,'20101001',0.08);

INSERT INTO TD VALUES (412,002,'20090101',0.09);
INSERT INTO TD VALUES (412,002,'20100901',0.04);

quote:

---

Originally posted by sunitabeck

I made the following changes (after removing all rows from table TD).

INSERT INTO TD VALUES (411,001,'20090701',0.06);
INSERT INTO TD VALUES (411,001,'20101001',0.08);

INSERT INTO TD VALUES (412,002,'20090101',0.09);
INSERT INTO TD VALUES (412,002,'20100901',0.04);

I don't know if those are the right things to do. The first change because I thought 577 is associated with 411 and 01 rather than 411 and 02.

The second change because the dates for 578 start in 20090101, so the way the code is written, it expects to find a CatStartDate earlier than or equal to that date.

---