How to find the hierarchy of Supervisor names of an employee from latest supervisor to all previous-CodePudding

Is there any way to find the desired output without using case function?

Code for creating employee_mapping table in which supervisor name and activation and deactivation date is present:

CREATE TABLE IF NOT EXISTS Employee_mapping (
    `emp_id` INT,
    `Supervisor_name` VARCHAR(7) CHARACTER SET utf8,
    `record_creation_time` Date,
    `deactivation_time` Date,
    `Is_active_record` VARCHAR(10) CHARACTER SET utf8
);
INSERT INTO Employee_mapping VALUES
    (101,'Lorentz','2017-05-04','2017-05-21','Non_active'),
    (101,'Hunold','2017-05-22','2017-05-24','Non_active'),
    (101,'De Haan','2017-05-25','2018-11-15','Non_active'),
    (101,'King','2018-11-16','2020-02-15','Active'),
    (102,'Hunold','2018-04-22','2019-08-25','Non_active'),
    (102,'De Haan','2019-08-26','2020-07-26','Non_active'),
    (102,'Austin','2020-07-28','2020-10-06','Acive'),
    (103,'Austin','2016-05-04','2016-08-23','Active');

It's output is:

emp_id  Supervisor_name  record_creation_date   deactivation_time   Is_active_record
101     Lorentz          5/4/2017               5/21/2017           Non_active
101     Hunold           5/22/2017              5/24/2017           Non_active
101     De Haan          5/25/2017              11/15/2018          Non_active
101     King             11/16/2018             2/15/2020           Active
102     Hunold           4/22/2018              8/25/2019           Non_active
102     De Haan          8/26/2019              7/26/2020           Non_active
102     Austin           7/28/2020              10/6/2020           Acive
103     Austin           5/4/2016               8/23/2016           Active

Code for creating employee_details table:

CREATE TABLE IF NOT EXISTS Employee_details (
    `emp_id` INT,
    `employee_name` VARCHAR(9) CHARACTER SET utf8,
    `emp_dept` VARCHAR(7) CHARACTER SET utf8,
    `dept_id` INT
);
INSERT INTO Employee_details VALUES
    (101,'Diana','HR',554),
    (101,'Diana','HR',554),
    (101,'Diana','Finance',558),
    (101,'Diana','HR',554),
    (102,'Alexander','Finance',558),
    (102,'Alexander','HR',554),
    (102,'Alexander','IT',332),
    (103,'Bruce','IT',332);

It's output is:

emp_id  employee_name   emp_dept    dept_id
101     Diana           HR          554
101     Diana           HR          554
101     Diana           Finance     558
101     Diana           HR          554
102     Alexander       Finance     558
102     Alexander       HR          554
102     Alexander       IT          332
103     Bruce           IT          332

Desired output:

emp_id  employee_name   Supervisor_name_hierarchy   
101     Diana           King>De Haan>Hunold>Lorentz 
102     Alexander       Austin>De Haan>Hunold   
103     Bruce           Austin

CodePudding user response：

My code with case function is:

select x.emp_id, t1.employee_name ,x.supervisor_name_hierarchy from
(SELECT emp_id,
       CASE WHEN EXISTS (SELECT 2 FROM Employee_mapping d2
                         WHERE d1.record_creation_time < d2.deactivation_time And
                               d1.deactivation_time > d2.record_creation_time AND
                               d1.emp_id = d2.emp_id)
            THEN ( group_concat(supervisor_name order by deactivation_time separator '>>'))
             ELSE supervisor_name END AS supervisor_name_hierarchy
            
FROM Employee_mapping d1
group BY emp_id) x

 inner join employee_map_details t1 using (emp_id)
 group BY emp_id;

CodePudding user response：

Using a distinct and descending order in the group_concat helps.

SELECT 
  emp.emp_id
, emp.employee_name
, group_concat(distinct map.Supervisor_name order by deactivation_time desc separator '>') as supervisor_name_hierarchy
FROM Employee_details emp
LEFT JOIN Employee_mapping map USING(emp_id)
GROUP BY emp.emp_id, emp.employee_name
ORDER BY emp.emp_id

emp_id | employee_name | supervisor_name_hierarchy  
-----: | :------------ | :--------------------------
   101 | Diana         | King>De Haan>Hunold>Lorentz
   102 | Alexander     | Austin>De Haan>Hunold      
   103 | Bruce         | Austin

db<>fiddle here