Avatar of BoggyBayouBoy
BoggyBayouBoy asked on

CTE - Recursive Query Question

Hello, I have a recursive query that I want to return the sum of hours at each level of a tree structure.  The tree structure contains Project tasks.  

For example at level 0 I have Project A.  At level 1 - I have project phases such as Design and Develop.  At level 2, I have tasks that map to a phase, and so on.  

My data is pretty simple:
TaskUID, TaskParentUID, TaskName, TaskWork. - where TaskParentUID is the parent task.  At the highest level of the tree, the ParentUID equals the TaskUID.  

My CTE looks like this
WITH CTE_Sample (TaskParentUID, TaskUID, TaskName, TaskWork) AS
(
      SELECT   TaskParentUID, TaskUID, TaskName, sum(TaskWork)
      FROM     MSP_EpmTask_UserView
      WHERE    TaskParentUID = TaskUID
and ProjectUID = '8622C06F-8C90-40D2-A4EB-59FF0F0AF7DA'
Group by TaskWork
 UNION ALL
      SELECT   a.TaskParentUID, a.TaskUID, a.TaskName, sum(a.TaskWork)
      FROM     MSP_EpmTask_UserView a
 INNER JOIN CTE_Sample ON a.TaskParentUID = CTE_Sample.TaskUID
where a.ProjectUID = '8622C06F-8C90-40D2-A4EB-59FF0F0AF7DA'
and a.TaskParentUID <> a.TaskUID
Group by a.TaskWork
)
SELECT TaskParentUID, TaskUID, TaskName
FROM CTE_Sample

The sum and group by expressions are causing syntax errors.  Help much appreciated!
DatabasesMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
BoggyBayouBoy

8/22/2022 - Mon
Thomasian

hi BoggyBayouBoy,

You should group by fields w/o aggregate functions

Try
WITH CTE_Sample (TaskParentUID, TaskUID, TaskName, TaskWork) AS
(
      SELECT   TaskParentUID, TaskUID, TaskName, sum(TaskWork)
      FROM     MSP_EpmTask_UserView
      WHERE    TaskParentUID = TaskUID
and ProjectUID = '8622C06F-8C90-40D2-A4EB-59FF0F0AF7DA'
Group by TaskParentUID, TaskUID, TaskName
 UNION ALL
      SELECT   a.TaskParentUID, a.TaskUID, a.TaskName, sum(a.TaskWork)
      FROM     MSP_EpmTask_UserView a
 INNER JOIN CTE_Sample ON a.TaskParentUID = CTE_Sample.TaskUID
where a.ProjectUID = '8622C06F-8C90-40D2-A4EB-59FF0F0AF7DA'
and a.TaskParentUID <> a.TaskUID
Group by a.TaskParentUID, a.TaskUID, a.TaskName
)
SELECT TaskParentUID, TaskUID, TaskName
FROM CTE_Sample

Open in new window

ASKER
BoggyBayouBoy

thanks... I get the following error:

Msg 467, Level 16, State 1, Line 1
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive common table expression 'CTE_Sample'.
ASKER CERTIFIED SOLUTION
Thomasian

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
BoggyBayouBoy

Thanks for the help... I am still struggling to get the right query.... but this helps me move forward.
Your help has saved me hundreds of hours of internet surfing.
fblack61