Блог

Главная - Вопросы по программированию - Как показать количество прямых и косвенных отчетов для каждого менеджера?

Как показать количество прямых и косвенных отчетов для каждого менеджера?

#sql #sql-server

#sql #sql-сервер

Вопрос:

У меня есть фрейм данных с идентификатором сотрудника, идентификатором менеджера и электронной почтой менеджера, и доступны электронные письма нескольких конкретных менеджеров, которые я хочу оценить.

До сих пор мне удалось подсчитать совокупное количество прямых и косвенных отчетов, однако я не могу найти способ сгруппировать по начальному линейному менеджеру и получить общее количество прямых и косвенных отчетов для каждого из них.

Примерные данные:

идентификатор сотрудника идентификатор сотрудника менеджера электронная почта линейного менеджера Эл. адрес
234567 3456 marco.dull@email.com ann@email.com
699999 234567 ann@email.com smith@email.com
33333 3456 marco.dull@email.com julian@email.com
666666 234567 ann@email.com john@email.com**

Выходной сигнал:

менеджер прямые отчеты косвенные отчеты
marco.dull@email.com 2 2

Это мой код до сих пор:

 select 0 as match_found,*
into #data
from clean.allemployees a
where a.reportingdate='31 oct 2020'



update d set d.match_found=1
from #data d
where d.EmailAddress in ('email1@goog.com', 'email2@goog.com', 'email3@goog.com')


update d set d.match_found=1
from #data d
where d.LineManager_EmployeeID in (
    select d.EmployeeID
    from #data d
    where d.match_found=1
)


select *
from #data d
where d.LineManager_EmployeeID in (
    select d.EmployeeID
    from #data d
    where d.match_found=1
)


select count(distinct JobTitle)
from #data d
where d.LineManager_EmployeeID in (
    select d.EmployeeID
    from #data d
    where d.match_found=1
)

Есть какие-нибудь предложения?

Комментарии:

1. пожалуйста, предоставьте примерные данные и желаемый результат

2. этого достаточно? Спасибо

Ответ №1:

если у вас есть только 1 уровень косвенного менеджера, вы просто можете использовать join и group примерно так:

 DROP TABLE IF EXISTS #employees;
CREATE TABLE #employees
    (
        employeeid          INT
        , manageremployeeid INT
        , linemanageremail  VARCHAR(100)
        , emailaddress      VARCHAR(100)
    );

INSERT into #employees values
(234567     ,3456   ,'marco.dull@email.com','ann@email.com'     )
,(699999    ,234567 ,'ann@email.com','smith@email.com'          )
,(33333     ,3456   ,'marco.dull@email.com','julian@email.com'  )
,(666666    ,234567 ,'ann@email.com','john@email.com'           )

 
 SELECT e.linemanageremail 
    , COUNT(DISTINCT e.employeeid) [direct manger] 
    , COUNT(CASE WHEN mgr.employeeid IS NOT NULL THEN 1 END) AS [indirect manger]
FROM #employees AS e
LEFT JOIN #employees mgr
    ON e.emailaddress = mgr.linemanageremail
GROUP BY e.linemanageremail

но если вы считаете косвенных менеджеров на более глубоких уровнях и считаете независимых менеджеров уровня 1, уровня 2 и т. Д., Вам Нужно использовать рекурсивный cte и pivot, однако вам нужно знать, насколько высокий уровень

 ; WITH cte AS (
SELECT * ,0 level 
FROM #employees AS e
UNION ALL 

SELECT e.* , level   1 AS level FROM cte
JOIN #employees AS e
 ON cte.manageremployeeid = e.employeeid
)

SELECT * FROM 
(
SELECT cte.linemanageremail ,cte.level , COUNT(*) AS mgrc
FROM cte
GROUP BY cte.linemanageremail ,cte.level
) AS source 
PIVOT (
sum(mgrc)
FOR level IN ([0], [1])
 )AS pt;

если у вас есть более глубокие косвенные менеджеры, но вы все равно хотите подсчитать их в группе ‘indiredt manager’, вы можете изменить приведенный выше запрос на этот:

 ; WITH cte AS (
SELECT * ,'direct  ' level 
FROM #employees AS e
UNION ALL 

SELECT e.* , 'indirect' level 
FROM cte
JOIN #employees AS e
 ON cte.manageremployeeid = e.employeeid
)

SELECT * FROM 
(
SELECT cte.linemanageremail ,cte.level , COUNT(*) AS mgrc
FROM cte
GROUP BY cte.linemanageremail ,cte.level
) AS source 
PIVOT (
sum(mgrc)
FOR level IN ([direct], [indirect])
 )AS pt;