Running total in recursion

  • CREATE TABLE #tblTasks

    (

    TaskID int,

    BasedOn int,

    Interval int

    )

    INSERT INTO #tblTasks

    (TaskID, BasedOn, Interval)

    SELECT 1, 2, 5 UNION ALL

    SELECT 2, 3, 3 UNION ALL

    SELECT 3, 4, 2

    ;WITH rCTE(TaskID, BasedOn, Interval, TaskLevel) AS

    (

    SELECT TaskID, BasedOn, Interval, 1 AS TaskLevel

    FROM #tblTasks

    WHERE TaskID = 1

    UNION ALL

    SELECT e.TaskID, e.BasedOn, e.Interval, TaskLevel + 1

    FROM #tblTasks e

    INNER JOIN rCTE c ON e.TaskID = c.BasedOn

    )

    SELECT * FROM rCTE

    DROP Table #tblTasks

    The code above produces this output:

    TaskID___BasedOn___Interval___TaskLevel

    1________2_________5________1

    2________3_________3________2

    3________4_________2________3

    I need a running total of the interval column. So I need a 5th column which, using the example above would show

    TaskID___BasedOn___Interval___TaskLevel___TotalInterval

    1________2_________5________1__________5

    2________3_________3________2__________8

    3________4_________2________3__________10

    How can I create the TotalInterval column? Thanks for any help.

  • ;WITH rCTE (TaskID, BasedOn, Interval, TaskLevel, TotalInterval) AS (

    SELECT TaskID, BasedOn, Interval,

    1 AS TaskLevel,

    TotalInterval = Interval

    FROM #tblTasks

    WHERE TaskID = 1

    UNION ALL

    SELECT e.TaskID, e.BasedOn, e.Interval,

    c.TaskLevel + 1,

    TotalInterval = c.TotalInterval + e.Interval

    FROM #tblTasks e

    INNER JOIN rCTE c ON e.TaskID = c.TaskID + 1

    )

    SELECT * FROM rCTE

    ;WITH rCTE (TaskID, BasedOn, Interval, TaskLevel, TotalInterval) AS (

    SELECT TaskID, BasedOn, Interval,

    1 AS TaskLevel,

    TotalInterval = Interval

    FROM #tblTasks

    WHERE TaskID = 1

    UNION ALL

    SELECT e.TaskID, e.BasedOn, e.Interval,

    c.TaskLevel + 1,

    TotalInterval = c.TotalInterval + e.Interval

    FROM #tblTasks e

    INNER JOIN rCTE c ON e.TaskID = c.BasedOn

    )

    SELECT * FROM rCTE

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thank you.

  • You're welcome. Any issues, questions?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply