Recursive RunningTotal calculate

CREATE TABLE #Pmts
(tran_date DATETIME PRIMARY KEY CLUSTERED
,Calc_interest_accrued MONEY, Interest_paid MONEY
,TotalInterestPaid MONEY, TotalInterestAccrued MONEY
,TotalInterestDue AS (TotalInterestAccrued - TotalInterestPaid))

INSERT INTO #Pmts (tran_date, Calc_interest_accrued, Interest_paid
,TotalInterestPaid, TotalInterestAccrued)
SELECT '2012-09-08',54.06,NULL,0,0
UNION ALL SELECT '2012-09-10',54.06,NULL,0,0
UNION ALL SELECT '2012-09-11',24.04,112.35,0,0
UNION ALL SELECT '2012-09-13',23.67,20.12,0,0
UNION ALL SELECT '2012-09-14',23.67,NULL,0,0

DECLARE @InterestPaid MONEY, @InterestAccrued MONEY
SELECT @InterestPaid = 0, @InterestAccrued = 0

UPDATE #Pmts
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
,@InterestAccrued = @InterestAccrued + Calc_interest_accrued

SELECT * FROM #Pmts

DROP TABLE #Pmts

dwain.c (10/28/2012)

I'm thinking that you may have your interest accured and interest paid terminology a little mixed up here.

Take a look at the following Quirky Update (QU) method for calculating what I think you need.

CREATE TABLE #Pmts
    (tran_date DATETIME PRIMARY KEY CLUSTERED
    ,Calc_interest_accrued MONEY, Interest_paid MONEY
    ,TotalInterestPaid MONEY, TotalInterestAccrued MONEY
    ,TotalInterestDue AS (TotalInterestAccrued - TotalInterestPaid))

INSERT INTO #Pmts (tran_date, Calc_interest_accrued, Interest_paid
    ,TotalInterestPaid, TotalInterestAccrued)
SELECT '2012-09-08',54.06,NULL,0,0
UNION ALL SELECT '2012-09-10',54.06,NULL,0,0
UNION ALL SELECT '2012-09-11',24.04,112.35,0,0
UNION ALL SELECT '2012-09-13',23.67,20.12,0,0
UNION ALL SELECT '2012-09-14',23.67,NULL,0,0

DECLARE @InterestPaid MONEY, @InterestAccrued MONEY
SELECT @InterestPaid = 0, @InterestAccrued = 0

UPDATE #Pmts
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
    ,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
    ,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
    ,@InterestAccrued = @InterestAccrued + Calc_interest_accrued

SELECT * FROM #Pmts

DROP TABLE #Pmts

Note that QU requires the CLUSTERED index on your date column and it will be much faster than any recursive approach to solving this problem.

Even though it doesn't produce exactly the results you need, I'm hoping it puts you on the right track.

UPDATE tgt
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
,@InterestAccrued = @InterestAccrued + Calc_interest_accrued
FROM #Pmts tgt WITH(TABLOCKX)
OPTION (MAXDOP 1)
;

UPDATE #Pmts WITH(TABLOCK)
SET TotalInterestPaid = ISNULL(@InterestPaid, 0) + TotalInterestPaid
,TotalInterestAccrued = TotalInterestAccrued + @InterestAccrued
,@InterestPaid = ISNULL(Interest_paid, 0) + @InterestPaid
,@InterestAccrued = @InterestAccrued + Calc_interest_accrued
OPTION (MAXDOP 1)