troubleshooting Question

CTE - Recursive Query Question

Avatar of BoggyBayouBoy
BoggyBayouBoy asked on
DatabasesMicrosoft SQL Server 2005
5 Comments1 Solution1548 ViewsLast Modified:
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!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros