Recursive Query needed? - 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)

Recursive Query needed?

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-02-28 : 16:17:47

I have data that looks like this.

TaskID, TaskName, ParentTaskID, Hours
1, Design Phase, null, null
2, Prepare test Plan, 1, 25
3, Prepare Specifications, 1, null
4, Prepare Data Specification, 3, 10
5, Prepare UI Specification, 3, 15

I want to return the totals at each level of the tree.

1, Design Phase, null, 50
2, Prepare test Plan, 1, 25
3, Prepare Specifications, 1, 25
4, Prepare Data Specification, 3, 10
5, Prepare UI Specification, 3, 15

help appreciated THANKS!!!!

John W Goodson

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-28 : 17:43:56

[code]
select *
from task t
cross apply
(
select TotalHours = sum(Hours)
from task x
where x.TaskID >= t.TaskID
) a
[/code]

EDIT : missed out the alias a

KH
[spoiler]Time is always against us[/spoiler]

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-02-28 : 19:11:10

Thanks but this doesn't parse?

John W Goodson

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-02-28 : 19:47:15

Are you getting an error when running? If so you need to replace the word 'task' with your table name. Or are you referring to a different problem, like not showing up in one entry?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-28 : 21:25:19

quote:
Originally posted by boggyboy

Thanks but this doesn't parse?

John W Goodson

edit my post.

KH
[spoiler]Time is always against us[/spoiler]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-01 : 02:24:34

[code]SELECT *,
TotalHours = (SELECT SUM(Hours)
FROM task AS X
WHERE X.TaskID >= T.TaskID)
FROM Task AS T
[/code]

JohnBGood
Starting Member

48 Posts

Posted - 2010-03-01 : 07:34:41

I really appreciate the help. In my real data the TASKID's are UniqueIdentifiers - so the >= comparison would not work

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 02:38:08

quote:
Originally posted by JohnBGood

I really appreciate the help. In my real data the TASKID's are UniqueIdentifiers - so the >= comparison would not work

It should work. Did you get any error?

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 05:26:41

quote:
Originally posted by madhivanan

quote:
Originally posted by JohnBGood

I really appreciate the help. In my real data the TASKID's are UniqueIdentifiers - so the >= comparison would not work

It should work. Did you get any error?

Madhivanan

Failing to plan is Planning to fail

See the OP problem. GUID is not sequential in a table but IDENTITY is seq.
TaskID is increment in the table.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 06:22:32

quote:
Originally posted by ms65g

quote:
Originally posted by madhivanan

quote:
Originally posted by JohnBGood

I really appreciate the help. In my real data the TASKID's are UniqueIdentifiers - so the >= comparison would not work

It should work. Did you get any error?

Madhivanan

Failing to plan is Planning to fail

See the OP problem. GUID is not sequential in a table but IDENTITY is seq.
TaskID is increment in the table.

Thanks. But the sample and expected result show numbers for TaskId column. OP needs to clarify this

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources