#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
    90
    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 threadwatch logo seochat tools logo