fetch records by comparing Hierarchy levels in SQL table

Question

Hi,

I have table called EmployeeHierarchy where we used to store data of each employee of our Organization and reporting hierarchy level in below format. Each employee will have reporting hierarchy levels,

Currently i have 7 hierarchy levels in my table, but it would expand later. Each employee will have his own ID at one level and after that if any levels remains then it would be NULL. Please look at the below table structure for reference.

Hierarchy tables structure to be read as

EmpId --> Current Employee
L1EmpId --> Top Hierarchy
L2EmpId --> 2nd Top Hierarchy
LxEmpId --> CurrentEmployee (same ID as EmpID)

DECLARE @employee TABLE (Eid INT, Ename VARCHAR(50),   
L1EmpId INT, L1EmpName VARCHAR(50), L1DepName VARCHAR(50),  
L2EmpId INT, L2Empnme VARCHAR(50), L2DepName VARCHAR(50),  
L3EmpId INT, L3EmpName VARCHAR(50), L3DepName VARCHAR(50),  
L4EmpId INT, L4EmpName VARCHAR(50), L4DepName VARCHAR(50),  
L5EmpId INT, L5EmpName VARCHAR(50), L5DepName VARCHAR(50))  
  
INSERT INTO @employee VALUES (101,'AAA', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',101, 'AAA','Sales',NULL, NULL,NULL)  
INSERT INTO @employee VALUES (102,'BBB', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',102, 'BBB', 'Service',NULL, NULL,NULL,NULL, NULL,NULL)  
INSERT INTO @employee VALUES (103,'CCC', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',102, 'BBB', 'Service',103, 'CCC', 'Manamgent')  
  
INSERT INTO @employee VALUES (501,'MMM', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',NULL, NULL,NULL,NULL, NULL,NULL)  
INSERT INTO @employee VALUES (1001,'YYY', 10001, 'ZZZ', 'Head of IT',NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL)  
INSERT INTO @employee VALUES (10001,'ZZZ', NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL)  
  
SELECT * FROM @employee  

My requirement here is, i need to write a query to extract ONE LEVEL MINUS (which mean immediate hierarchy) from where the current employee ID in any levels.

For above records, i need results as below

Looking forward for the help to write SQL query for this situation. Thanks in advance.

Answer 1

Try one of approaches:

; with Q as  
(  
 select *, row_number() over (partition by Eid order by i desc) rn  
 from @employee  
 cross apply (values   
   ( 1, L1EmpId, L1EmpName, L1DepName ),  
   ( 2, L2EmpId, L2Empnme , L2DepName ),  
   ( 3, L3EmpId, L3EmpName, L3DepName ),  
   ( 4, L4EmpId, L4EmpName, L4DepName ),  
   ( 5, L5EmpId, L5EmpName, L5DepName )  
 ) t(i, id, n, d)   
 where id is not null  
)  
select e.Eid, e.Ename, id as ImmediateLvlEmpId, n as ImmediateLvlEmpName, d as ImmediateLvlEmpDept  
from @employee e  
left join Q on Q.Eid = e.Eid and rn = 2  
order by Eid  

However, it is not clear why the rules are not applied for Eid=1001 in your sample.

Answer 2

 DECLARE @employee TABLE (Eid INT, Ename VARCHAR(50),   
 L1EmpId INT, L1EmpName VARCHAR(50), L1DepName VARCHAR(50),  
 L2EmpId INT, L2EmpName VARCHAR(50), L2DepName VARCHAR(50),  
 L3EmpId INT, L3EmpName VARCHAR(50), L3DepName VARCHAR(50),  
 L4EmpId INT, L4EmpName VARCHAR(50), L4DepName VARCHAR(50),  
 L5EmpId INT, L5EmpName VARCHAR(50), L5DepName VARCHAR(50))  
      
 INSERT INTO @employee VALUES (101,'AAA', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',101, 'AAA','Sales',NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (102,'BBB', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',102, 'BBB', 'Service',NULL, NULL,NULL,NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (103,'CCC', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',102, 'BBB', 'Service',103, 'CCC', 'Manamgent')  
      
 INSERT INTO @employee VALUES (501,'MMM', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',NULL, NULL,NULL,NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (1001,'YYY', 10001, 'ZZZ', 'Head of IT',NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (10001,'ZZZ', NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL)  
    
  ;with source as   
  (  
 SELECT Eid, Ename   
 ,Coalesce(Nullif(L5EmpId,Eid),  
 Nullif(L4EmpId,Eid),  
 Nullif(L3EmpId,Eid),  
 Nullif(L2EmpId,Eid),  
 Nullif(L1EmpId,Eid) ) managerId  
  
  ,Coalesce(Nullif(L5EmpName,Ename),  
 Nullif(L4EmpName,Ename),  
 Nullif(L3EmpName,Ename),  
 Nullif(L2EmpName,Ename),  
 Nullif(L1EmpName,Ename) ) managerName  
   
  
  
   ,Coalesce(  
   Case when L5EmpId=Eid then null else L5DepName end,  
   Case when L4EmpId=Eid then null else L4DepName end,  
   Case when L3EmpId=Eid then null else L3DepName end,  
   Case when L2EmpId=Eid then null else L2DepName end,  
   Case when L1EmpId=Eid then null else L1DepName end   
   
 ) DepName  
   
   
   
   
 FROM @employee  
 )  
  
  
  
Select   Eid,Ename,	managerId,	managerName,	DepName  
   
from source s  
  /*  
  
Eid	Ename	managerId	managerName	DepName  
101	AAA	501	MMM	Project Manager  
102	BBB	1001	YYY	Vice President  
103	CCC	102	BBB	Service  
501	MMM	1001	YYY	Vice President  
1001	YYY	10001	ZZZ	Head of IT  
10001	ZZZ	NULL	NULL	NULL  
*/  
  
  

Answer 3

 DECLARE @employee TABLE (Eid INT, Ename VARCHAR(50),   
 L1EmpId INT, L1EmpName VARCHAR(50), L1DepName VARCHAR(50),  
 L2EmpId INT, L2EmpName VARCHAR(50), L2DepName VARCHAR(50),  
 L3EmpId INT, L3EmpName VARCHAR(50), L3DepName VARCHAR(50),  
 L4EmpId INT, L4EmpName VARCHAR(50), L4DepName VARCHAR(50),  
 L5EmpId INT, L5EmpName VARCHAR(50), L5DepName VARCHAR(50))  
      
 INSERT INTO @employee VALUES (101,'AAA', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',101, 'AAA','Sales',NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (102,'BBB', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',102, 'BBB', 'Service',NULL, NULL,NULL,NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (103,'CCC', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',102, 'BBB', 'Service',103, 'CCC', 'Manamgent')  
      
 INSERT INTO @employee VALUES (501,'MMM', 10001, 'ZZZ', 'Head of IT',1001, 'YYY', 'Vice President',501, 'MMM', 'Project Manager',NULL, NULL,NULL,NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (1001,'YYY', 10001, 'ZZZ', 'Head of IT',NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL)  
 INSERT INTO @employee VALUES (10001,'ZZZ', NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL,NULL, NULL,NULL)  
    
   
  
  
   ; with mycte as  
 (  
  select  Eid, Ename ,mEid, mEmpName, DepName ,  
  row_number() over (partition by Eid order by seq  desc ) rn  
  from @employee  
  cross apply (values   
    ( L1EmpId, L1EmpName, L1DepName,1),  
    ( L2EmpId, L2EmpName, L2DepName,2 ),  
    ( L3EmpId, L3EmpName, L3DepName,3 ),  
    ( L4EmpId, L4EmpName, L4DepName,4 ),  
    ( L5EmpId, L5EmpName, L5DepName,5 )  
  ) t( mEid, mEmpName, DepName ,seq)   
 where (mEmpName is not null and mEmpName<> Ename)   
 )  
  
Select  Eid, Ename ,mEid, mEmpName, DepName   
From mycte  
where rn=1  
 Union all  
Select  Eid, Ename ,null as mEid, null as mEmpName, null as DepName    
from @employee  
where L1EmpId is null  
 /*  
 Eid	Ename	mEid	mEmpName	DepName  
101	AAA	501	MMM	Project Manager  
102	BBB	1001	YYY	Vice President  
103	CCC	102	BBB	Service  
501	MMM	1001	YYY	Vice President  
1001	YYY	10001	ZZZ	Head of IT  
10001	ZZZ	NULL	NULL	NULL  
 */