Employee hierarchy with expense

Question

Employee hierarchy with expense

celestine

Valued Member

Points: 63
More actions
September 1, 2019 at 2:34 pm

#3676169

Please help me to get the below output from table

Viewing 7 posts - 1 through 6 (of 6 total)

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

dbgaragedays Old Hand Points: 358 More actions · Answer 1

You need to include more information. Do you have any code at all? What are the table structures? Where is the team amount column?

celestine Valued Member Points: 63 More actions · Answer 2

sorry for ray team_amount

mike report to ray 200+matt report to mike 100

magan report to ray 50+ stephaine report to magan 25

ray team amount (200+100+50+25)=375

ray individual amount 250

total amount 250+375=625

pietlinden SSC Guru Points: 63488 More actions · Answer 3

Use a common table expression.

https://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 4

celestine wrote:

Please help me to get the below output from table

For future posts, please see the first link in my signature line below. Producing "readily consumable" data, as suggested in the linked article, will get you coded responses and in a whole lot less time.

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 5

Also, see the following article that provides a method to solve for most things that you could as of an "Adjacency List" Hierarchy.

https://www.sqlservercentral.com/articles/hierarchies-on-steroids-2-a-replacement-for-nested-sets-calculations-1

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

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

Look up the nested set model for hierarchies and trees. This will make your problem trivial. Also in the future, would you mind posting DDL instead of spreadsheet pictures? We really don't like the copy your data into our own machines we could have cut and pasted a properly done posting.

Please post DDL and follow ANSI/ISO standards when asking for help.