Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    13

    Question Unanswered: Sql Query for Team hierarchy

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

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •