Sql Query for Team hierarchy

neha101 · #1 (**permalink**) 01-26-07, 09:50

I have following hierarchy:

Product Manager -> Team Mangers (works under PM) -> MDMs (works under TMs) -> Reps (works under MDMs)

but not limited to these. we can have more later on.(E.g: Reps can give money to their team as well...)

Every Product Manager, Team Manger, MDM will have diff team.

PM allocates money to TMs. TMs Allocates money to MDMs ...and so on.

I have a query that Sum up total amount of money spent by one person (values coming from diff tables), in this case I just pass entered @username.

I have to write ASP code or a SQL Query in such a way that when user inputs Product Manager in text box and submit, then my page will calculate all the money spent by his Team Mangers + their MDMs + their Reps...

If they enter Team Manger then page will calculate all the money spent by his MDMs + their Reps...

I can do this in case if I know that the hierarchy is fixed but I cannot set limitation for my calculations. Hierarchy can increase, E.g: Reps can give money to their team as well...

Please Help..

Lexiflex · #2 (**permalink**) 01-26-07, 15:29

I'm hoping they're all in the same table because if the hierarchy increases you will otherwise have to include a new table in the query.

If this is the case you could make a recursive function (in ASP or on the db) like:

Code:

FUNCTION AmountSpent (@username)
AS
BEGIN
   SELECT @result = SUM(SpendMoney + AmountSpent(employee_name))
   FROM myTable
   WHERE manager = @username

   RETURN @result
END

It's a bit pseudo codish but I hope you catch my drift.
In red is the function calling itself to calculate a lever deeper in the hierarchy.

Warning: it's possible you can hit some max recursion limit...

Internet Services

Web Development

Digital Marketing

Consumer Tech