Hierarchical Data with joins

Question

Hierarchical Data with joins

ezimmerman 33749

Newbie

Points: 1
More actions
January 18, 2011 at 7:58 am

#230561

Let's say I have the following tables:
Task Table
-----------
Id [Guid]
Name [nvarchar(64)]
ParentId [Guid]
Time Entry Table
-----------------
Id [Guid]
EntryDate [DateTime]
HoursCharged [float]
TaskId [Guid]
1. I would like to load the entire hierarchy of a parent Id in a single call. I know this is possible, but I'm not sure how.
2. I would like to sum all hours charged for each task. Can I do this in #1's SQL string?
Best Regards,
Eric

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004683 More actions · Answer 1

ezimmerman 33749 (1/18/2011)
Let's say I have the following tables:
Task Table
-----------
Id [Guid]
Name [nvarchar(64)]
ParentId [Guid]
Time Entry Table
-----------------
Id [Guid]
EntryDate [DateTime]
HoursCharged [float]
TaskId [Guid]
1. I would like to load the entire hierarchy of a parent Id in a single call. I know this is possible, but I'm not sure how.
2. I would like to sum all hours charged for each task. Can I do this in #1's SQL string?
Best Regards,
Eric

Lookup "Recursive CTEs" in Books Online (the Help system that comes with SQL Server). That should do the trick for you.

If you provide some tables and data in the readily consumable format identified by the first link in my signature line below, we can give you a coded answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)