Home Forums SQL Server 2005 T-SQL (SS2K5) Recursive RunningTotal calculate RE: Recursive RunningTotal calculate

  • October 28, 2012 at 9:16 pm

    #1553587

    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.

    Not quite right. You need to modify the update to have a FROM clause from the target table so that you can add the MAXDOP option to prevent parallelism. Like this...

    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)

    ;

    The TablockX isn't required on a Temp Table but it'll bypass all sorts of row escalation to make leaner (memory-wise) and faster code.

    I haven't checked the rest of the code nor have my changes made it solve the OP's precise problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)