recursive update

Question

hi all,

I have this table:

DECLARE @tbl TABLE (EmpID INT, EmpIDChain INT, SupervisorID INT, ApproverLevel INT, ApproverID INT);

INSERT @tbl (EmpID, EmpIDChain, SupervisorID, ApproverLevel, ApproverID) VALUES

(1001, 1001, 2011, 0, null),
(1001, 2011, 2012, 1, null),
(1001, 2012, 2013, 2, null),
(1001, 2013, 2014, 3, null),
(1002, 1002, 2021, 1, null),
(1002, 2021, 2022, 2, null),
(1002, 2022, 2023, 2, null),
(1002, 2023, 2024, 3, null),
(1003, 1003, 2031, 2, null),
(1003, 2031, 2032, 3, null),
(1003, 2032, 2033, 3, null),
(1004, 1004, 2041, 4, null),
(1004, 2041, 2042, 5, null);
-- DDL and sample data population, end

It contains a chain of command grouped by EmpID, namely EmpID reports to EmpID 2011, EmpID 2011 reports to EmpID 2012, and so on.

The task is to update the ApproverID field such that from any EmpID group, the ApproverID will be the SupervisorID that has minimum ApproverLevel 2.

Therefore the updated table should look like this:
(1001, 1001, 2011, 0, 2012),
(1001, 2011, 2012, 1, 2012),
(1001, 2012, 2013, 2, 2012),
(1001, 2013, 2014, 3, 2012),
(1002, 1002, 2021, 1, 2021),
(1002, 2021, 2022, 2, 2021),
(1002, 2022, 2023, 2, 2021),
(1002, 2023, 2024, 3, 2021),
(1003, 1003, 2031, 2, 2031),
(1003, 2031, 2032, 3, 2031),
(1003, 2032, 2033, 3, 2031),
(1004, 1004, 2041, 4, 2041),
(1004, 2041, 2042, 5, 2041);

because in the 1001 group the first SupervisorID with ApproverLevel 2 is 2012, and so on.

I have tried a recursive query that populates a temp table but then I am not able to retain only the relevant entries in it.

Thanks,

elsvieta

Answer 1

Hi @elsvieta

If I understand correctly, you can try this.

;with T1 as(
  select *,row_number()over(partition by EmpID 
    order by case when ApproverLevel = 0 then 2 else 1 end,SupervisorID) as jud from tbl
),T2 as(
  select EmpID,SupervisorID as SID from T1 where jud = 1) 
update tbl set ApproverID = SID from tbl as A inner join T2 as B on A.EmpID = B.EmpID;
 
select * from tbl;

Output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Erland Sommarskog,

thank you for your answer. The updated Approver ID refers to the EmpID column, therefore the update version of the column is stating, correctly, that 2012 is the Approver of 1001. It is confusing, but I need the data this way because the reporting tool would not work with anything else.

Thanks again,

elsvieta