December 30, 2013 at 4:19 am
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.
December 30, 2013 at 5:16 am
;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
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
December 30, 2013 at 8:48 am
Thank you.
December 30, 2013 at 8:53 am
You're welcome. Any issues, questions?
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