#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    1
    Rep Power
    0

    Find All the users (manager and individuals) to top level manager


    I am stuck at the point where I do'nt understand how to get deeper in table

    I have a table "profiles"

    Userid Name MgrID
    1 A 0
    2 B 1
    3 C 2
    4 D 3
    5 E 4
    6 F 4
    7 G 5


    I need to join this with bugs to find bug count. 1 use case was simple where I need to count bugs for Top Manager "A"

    select p.login_name,
    count(case when b.priority = 'P0' then b.bug_id else NULL end) AS high,
    count(case when b.priority = 'P1' then b.bug_id else NULL end) AS med ,
    count(case when b.priority = 'P2' then b.bug_id else NULL end) AS low ,
    sum(case when b.priority in ('high' , 'med' , 'low' ) then 1 else NULL end) AS Total
    from bugs b
    left join profiles p
    on b.assigned_to = p.userid
    where p.userid in (select profiles.userid from profiles where profiles.MgrID != 0)
    group by p.login_name


    I am unable to think through how can i find count for let's say

    "B" as C reports to B, D reports to C, E and F reports to D

    so my count should be all high bugs for team B , C , D , E and F

    Please help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    85
    Rep Power
    3
    I am unable to think through how can i find count for let's say

    "B" as C reports to B, D reports to C, E and F reports to D

    so my count should be all high bugs for team B , C , D , E and F
    Hi Kelsey, i think it would be very helpful for you to explain what this means a little more clearly with an example and also to give us some idea of the structure of the bugs table

IMN logo majestic logo spyfu logo threadwatch logo seochat tools logo