Why doesnt this CTE work? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Why doesnt this CTE work?

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-09-03 : 08:39:30

CTE Newbie here trying to learn.

I have some hierarical data and using a CTE to sum the values. My query returns the incorrect total cost. For example, the total cost of 'Write Specs' should be 20, rather than 10.

DECLARE @foo TABLE (
[ID] INT
,[TimeByDay] datetime
, [Task Name] NVARCHAR(255)
, [ParentId] INT
, [cost] MONEY
)

INSERT @foo
SELECT 1, null,'Design Activities', null, 0
UNION SELECT 2,null, 'Write Specs', 1, 0
UNION SELECT 3,1/1/2009, 'Write UI Spec', 2, 5
UNION SELECT 3,1/2/2009, 'Write UI Spec', 2, 5
UNION SELECT 3,1/3/2009, 'Write UI Spec', 2, 5
UNION SELECT 3,1/4/2009, 'Write UI Spec', 2, 5
UNION SELECT 4,1/1/2009, 'Write Data Spec', 2, 5
UNION SELECT 4,1/2/2009, 'Write Data Spec', 2, 5
UNION SELECT 5,null, 'Build Prototypes', 1, 0
UNION SELECT 6,1/1/2009, 'Build Screen Mockkups', 5, 5
UNION SELECT 6,1/2/2009, 'Build Screen Mockkups', 5, 5

; WITH cte AS (

-- this is the part known as the anchor
SELECT
[ID] AS [Start_Id]
,[TimebyDay]
, [parentId] AS [Start_Parent_Id]
, [ID]
, [Task Name]
, [ParentId]
, [cost]
FROM
@foo f1
UNION ALL
-- this is the recursive part
SELECT
f1.[start_Id]
,f1.[TimebyDay]
, f1.[Start_Parent_Id]
, f2.[ID]
, f1.[task Name]
, f2.[parentId]
, f2.[cost]
FROM
@foo f2
JOIN cte f1 ON f1.[Id] = f2.[parentId]
--and f1.[TimeByDay] = f2.[TimeByDay]
)

--this is the part that returns the results.
SELECT
[start_Id] as ID
, [task Name] as TaskName
, [start_parent_Id]
, SUM([cost])
FROM
cte
GROUP BY
[start_Id]
, [task Name]
, [start_parent_Id]
OPTION (MAXRECURSION 0)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-03 : 08:46:09

I think the anchor part needs: where ParentId is null

No, you're never too old to Yak'n'Roll if you're too young to die.