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