SQL Script for Employee/manager Mismatch-CodePudding

We have a Employee table, and I read the contents of it to build a treeview control for employee hierarchy. The table structure is 1.FirstName 2.lastName 3.EmpNumber 4.Begindate 5.LastDateOfEmployement 6.ManagerId

Each employee reports to a managerId. When an Employee leaves the org the "lastdateofEmployement" is updated. We have a script to populate the treeview control of all employees , who has LastDateOfemployement as NUll. That works fine. However HR sometimes forgets to update the ManagerId of an employee, whose manager leaves the organization. So when our script runs, the treeview fails to populate.

Can someone PLEASE help me write a script where , if an employee A leaves an organization, anyone reporting to him/her, should have their managerID updated to A's Managerid.

Emp A Emp B Emp C

C Reports to B , and B reports to A. If B leaves the org (LastDateofEmployement is Not Null), the manager ID of C should be updated to A's Empnumber.

Please help!

CodePudding user response：

I'm assuming that managerID is the EmpNumber.

If this is the case you can try to join the table with itself for the update:

UPDATE lostEmployee
SET ManagerId = goneManager.ManagerId
FROM Employees lostEmployee
INNER JOIN Employees goneManager ON lostEmployee.ManagerId = goneManager.EmpNumber
WHERE goneManager.LastDateOfEmployement IS NOT NULL

Check this for a running example: DB Fiddle

CodePudding user response：

On the assumption that the data in your table is currently consistent (i.e. all employees report to a currently active employee), this should work:

/* TODO: replace with actual EmpNumber the departing manager */
declare @DepartingEmployee int = 123;

declare @NewManager int = (
   select ManagerID
   from dbo.Employee
   where EmpNumber = @DepartingEmployee
);

begin transaction;

   update dbo.Employee
   set ManagerID = @NewManager
   where ManagerID = @DepartingEmployee ;

   update dbo.Employee
   set LastDateOfEmployement = getdate()
   where EmpNumber = @DepartingEmployee;

commit transaction;

Given your question though, it might be a pretty big assumption to make that the table is currently consistent! To find situations where employees report to an inactive employee, you'll have to do a one-time fix-up. This should identify the inconsistent data:

select e.EmpNumber, m.EmpNumber, m.ManagerID, m.LastDateOfEmployement
from dbo.Employee as e
join dbo.Employee as m
    on e.ManagerID = m.EmpNumber
where m.LastDateOfEmployement is not null;

You can use the result set generated by that to drive updates. I leave that as an exercise for the reader.

Note - depending on how long you've gone without reparenting "orphaned" employees, you may have to go through the identify/update cycle multiple times. An example would be that if the org chart looked like A → B → C → D at some point (where "A → B" is read as "A reports to B") and both B and C have since left the org, the first pass would update A's manager to C (since C is B's manager) and a second pass would identify A as still reporting to a no longer active employee. Assuming that your tree is shallow, a multi pass approach is probably fine.