Can a CTE do this?? - 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)

Can a CTE do this??

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2009-09-02 : 21:42:14

I have project data in two tables. The first table contains time-phased ‘assignments’ at the lowest level and looks something like this.

AssignmentID,TimeByDay,TaskName,TaskID,Cost

1,1/1/2009, 'Write UI Spec', 3, 5
1,1/2/2009, 'Write UI Spec', 3, 5
1,1/3/2009, 'Write UI Spec', 3, 5
1,1/4/2009, 'Write UI Spec', 3, 5
2,1/1/2009, 'Write Data Spec', 4, 5
2,1/2/2009, 'Write Data Spec', 4, 5
3,1/1/2009, 'Build Screen Mockups', 6, 5
3,1/2/2009, 'Build Screen Mockups', 6, 5
4,1/1/2009, 'Build Engine Mockups', 7, 5
4,1/2/2009, 'Build Engine Mockups', 7, 5

the taskID (column 4) maps to taskID in table 2, which looks like the following:

TaskID,TaskName,ParentID
1, 'Design', null
2,'Write Specifications', 1
3,'Write UI Spec', 2
4,'Write Data Spec', 2
5,'Build Mockups', 1
6,'Build Screen Mockups', 5
7,'Build Engine Mockups', 5

Note how the parentID defines the hierarchy of tasks. For example, 'Write UI Spec' has a Parent Task of 'Write Specifications', which has a parent of ‘Design’.

I want to return data that returns the total cost for each task. Something like
1, 'Design', null, 50
2,'Write Specifications', 1, 30
3,'Write UI Spec', 2, 20
4,'Write Data Spec', 2, 10
5,'Build Mockups', 1, 20
6,'Build Screen Mockups', 5, 10
7,'Build Engine Mockups', 5, 10

I want to use a CTE but cant get my head around the syntax. Any help much appreciated!!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-09-03 : 01:15:41

yes u can use cte for this..........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources