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
      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

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