CODEWITHSUNDEEP
sql-servert-sql
DenStudent
DenStudent

Reputation: 928

SQL Server- get manager of employee (with exceptions)

I am currently stuck with a (for me) quite complicated problem.

I have created following table with cte's:

EmployeeID | OrganisationID | Level | Manager1 | Manager2 | Manager3 | Manager4 | Manager5
1          | 1001           | 1     | NULL     | NULL     | NULL     | NULL     | NULL     
8          | 1011           | 1     | 1        | NULL     | NULL     | NULL     | NULL     
2          | 1002           | 2     | 1        | 1        | NULL     | NULL     | NULL     
3          | 1002           | 2     | 1        | 1        | NULL     | NULL     | NULL     
4          | 1003           | 4     | 1        | 2        | NULL     | 4        | NULL     
5          | 1004           | 3     | NULL     | NULL     | 2        | NULL     | NULL     
6          | 1005           | 5     | 1        | NULL     | 2        | 6        | 6
7          | 1006           | 5     | NULL     | 7        | 7        | NULL     | NULL

What I want to get from this is the manager of each employee, based on their Level. So for someone from level 5, I need to get his Manager5 However, there are some conditions:

So what I would like as result would be:

EmployeeID | RealManager
1          | NULL      
8          | 1         
2          | 1         
3          | 1         
4          | 2         
5          | 2         
6          | 2         
7          | NULL

I only got this far

SELECT EmployeeID, 
Case    
    when level = 1 then Manager1
    when level = 2 then ISNULL(Manager2,Manager1)
    when level = 3 then ISNULL(Manager3,ISNULL(Manager2,Manager1))
    when level = 4 then ISNULL(Manager4,ISNULL(Manager3,ISNULL(Manager2,Manager1)))
    when level = 5 then ISNULL(Manager5,ISNULL(Manager4,ISNULL(Manager3,ISNULL(Manager2,Manager1))))
    else null
    END as RealManager
FROM cte

But this doesn't check the first condition (and it doesn't look quite performant).

Any sugestions or ideas?

Upvotes: 0

Views: 249

Answers (3)

codersl
codersl

Reputation: 2332

I would do something like this:

--set to 'null' all managers that have same value as employees
;with cte2 (EmployeeID, Level, Manager1, Manager2, Manager3, Manager4, Manager5)
as
(
    select
        EmployeeID
        ,Level
        ,Manager1
        ,case when Manager2 = EmployeeID then null else Manager2 end as Manager2
        ,case when Manager3 = EmployeeID then null else Manager3 end as Manager3
        ,case when Manager4 = EmployeeID then null else Manager4 end as Manager4
        ,case when Manager5 = EmployeeID then null else Manager5 end as Manager5
    from cte
)

--now just select the first non-null manager based on employee's level
select
    EmployeeID
    ,case Level
        when 1 then Manager1
        when 2 then coalesce(Manager2, Manager1)
        when 3 then coalesce(Manager3, Manager2, Manager1)
        when 4 then coalesce(Manager4, Manager3, Manager2, Manager1)
        when 5 then coalesce(Manager5, Manager4, Manager3, Manager2, Manager1)
    end as RealManager
from cte2

Upvotes: 1

Praveen ND
Praveen ND

Reputation: 560

Please try to make use of below code:

    DECLARE @Table TABLE
    (EmployeeID INT,OrganisationID INT,Level INT,Manager1 INT,Manager2 INT,Manager3 INT,Manager4 INT,Manager5 INT)

    INSERT INTO @Table VALUES
    (1,1001,1,NULL,NULL,NULL,NULL,NULL),    
    (8,1011,1,1   ,NULL,NULL,NULL,NULL),    
    (2,1002,2,1   ,1   ,NULL,NULL,NULL),    
    (3,1002,2,1   ,1   ,NULL,NULL,NULL),    
    (4,1003,4,1   ,2   ,NULL,4   ,NULL),    
    (5,1004,3,NULL,NULL,2   ,NULL,NULL),    
    (6,1005,5,1   ,NULL,2   ,6   ,6   ),
    (7,1006,5,NULL,7   ,7   ,NULL,NULL)


    SELECT EmployeeID, 
    CASE
        WHEN level = 1 THEN  NULLIF(Manager1,EmployeeID)
        WHEN level = 2 THEN  COALESCE(NULLIF(Manager2,EmployeeID),NULLIF(Manager1,EmployeeID))
        WHEN level = 3 THEN  COALESCE(NULLIF(Manager3,EmployeeID),NULLIF(Manager2,EmployeeID),NULLIF(Manager1,EmployeeID))
        WHEN level = 4 THEN  COALESCE(NULLIF(Manager4,EmployeeID),NULLIF(Manager3,EmployeeID),NULLIF(Manager2,EmployeeID),NULLIF(Manager1,EmployeeID))
        WHEN level = 5 THEN  COALESCE(NULLIF(Manager5,EmployeeID),NULLIF(Manager4,EmployeeID),NULLIF(Manager3,EmployeeID),NULLIF(Manager2,EmployeeID),NULLIF(Manager1,EmployeeID))
        END AS Manager
    FROM @Table

Upvotes: 1

TriV
TriV

Reputation: 5148

Try this

DECLARE @Employee AS TABLE
(
   EmployeeID int ,
   OrganisationID int,
   Level int,
   Manager1 int,
   Manager2 int,
   Manager3 int,
   Manager4 int,
   Manager5 int
)

INSERT INTO @Employee VALUES(1          , 1001           , 1     , NULL     , NULL     , NULL     , NULL     , NULL    )
INSERT INTO @Employee VALUES(8          , 1011           , 1     , 1        , NULL     , NULL     , NULL     , NULL     )
INSERT INTO @Employee VALUES(2          , 1002           , 2     , 1        , 1        , NULL     , NULL     , NULL     )
INSERT INTO @Employee VALUES(3          , 1002           , 2     , 1        , 1        , NULL     , NULL     , NULL     )
INSERT INTO @Employee VALUES(4          , 1003           , 4     , 1        , 2        , NULL     , 4        , NULL     )
INSERT INTO @Employee VALUES(5          , 1004           , 3     , NULL     , NULL     , 2        , NULL     , NULL   )
INSERT INTO @Employee VALUES(6          , 1005           , 5     , 1        , NULL     , 2        , 6        , 6)
INSERT INTO @Employee VALUES(7          , 1006           , 5     , NULL     , 7        , 7        , NULL     , NULL   )

;WITH temps AS
(SELECT e.EmployeeID,e.Level, cr.ManagerId, cr.ManagerLevel 
FROM @Employee e
CROSS APPLY
(
   SELECT e.Manager1 AS ManagerId,  1 AS ManagerLevel 
   UNION ALL
   SELECT e.Manager2,  2 AS ManagerLevel 
   UNION ALL
   SELECT e.Manager3,  3 AS ManagerLevel 
   UNION ALL
   SELECT e.Manager4,  4 AS ManagerLevel 
   UNION ALL
   SELECT e.Manager5,  5 AS ManagerLevel 
) AS cr
)
SELECT  e.EmployeeID, t.ManagerId
FROM  @Employee e
LEFT JOIN
(
   SELECT DISTINCT t.EmployeeID , First_Value(t.ManagerId) OVER (PARTITION BY t.EmployeeID ORDER BY t.ManagerLevel DESC) AS ManagerId
   FROM temps t 
   WHERE t.ManagerLevel <= t.Level AND t.ManagerId IS NOT NULL AND t.ManagerId <> t.EmployeeID
) t ON e.EmployeeID = t.EmployeeID

Upvotes: 1

Related Questions