By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sys_user
([sys_id] int, [name] varchar(17), [title] varchar(10), [manager] int)
;
INSERT INTO sys_user
([sys_id], [name], [title], [manager])
VALUES (555789, 'Tina Belcher', 'Contractor', 123456)
, (123456, 'Bob Belcher', 'Manager', 654321)
, (654321, 'Calvin Fischoeder', 'SVP', 997755)
-- , (997755, 'Adam Ant', 'CEO', null)
-- , (997755, 'Adam Ant', 'CEO', 997755)
;
3 rows affected
with cte as (
select su.sys_id, su.name, su.title, su.manager, 1 as lev, 0 as hit_svp
from sys_user su
where su.Title like '%contractor%'
union all
select su.sys_id, su.name, su.title, su.manager, lev + 1,
(case when su.title like '%SVP%' then 1 else 0 end) as hit_svp
from sys_user su join
cte
on cte.manager = su.sys_id
where cte.hit_svp = 0
)
select * from cte
| sys_id | name | title | manager | lev | hit_svp |
|---|---|---|---|---|---|
| 555789 | Tina Belcher | Contractor | 123456 | 1 | 0 |
| 123456 | Bob Belcher | Manager | 654321 | 2 | 0 |
| 654321 | Calvin Fischoeder | SVP | 997755 | 3 | 1 |
with closure(manager_id, report_id, degree, is_managing_SVP, is_reporting_svp) as (
select sys_id
, sys_id
, 0
, case when title like '%SVP%' then 1 else 0 end
, case when title like '%SVP%' then 1 else 0 end
from sys_user
union all
select cur.manager_id
, nxt.sys_id
, cur.degree+1
, cur.is_managing_SVP
, case when nxt.title like '%SVP%' then 1 else 0 end
from closure cur
join sys_user nxt
on nxt.manager = cur.report_id
and nxt.sys_id <> nxt.manager
)
select * from closure
| manager_id | report_id | degree | is_managing_SVP | is_reporting_svp |
|---|---|---|---|---|
| 555789 | 555789 | 0 | 0 | 0 |
| 123456 | 123456 | 0 | 0 | 0 |
| 654321 | 654321 | 0 | 1 | 1 |
| 654321 | 123456 | 1 | 1 | 0 |
| 654321 | 555789 | 2 | 1 | 0 |
| 123456 | 555789 | 1 | 0 | 0 |
with closure(manager_id, report_id, degree, is_managing_SVP, is_reporting_svp) as (
select sys_id
, sys_id
, 0
, case when title like '%SVP%' then 1 else 0 end
, case when title like '%SVP%' then 1 else 0 end
from sys_user
union all
select nxt.manager
, cur.report_id
, cur.degree+1
, case when mgr.title like '%SVP%' then 1 else 0 end
, cur.is_reporting_SVP
from closure cur
join sys_user nxt
on nxt.sys_id = cur.manager_id
and nxt.sys_id <> nxt.manager
join sys_user mgr
on mgr.sys_id = nxt.manager
)
select * from closure
| manager_id | report_id | degree | is_managing_SVP | is_reporting_svp |
|---|---|---|---|---|
| 555789 | 555789 | 0 | 0 | 0 |
| 123456 | 123456 | 0 | 0 | 0 |
| 654321 | 654321 | 0 | 1 | 1 |
| 654321 | 123456 | 1 | 1 | 0 |
| 123456 | 555789 | 1 | 0 | 0 |
| 654321 | 555789 | 2 | 1 | 0 |
with closure(manager_id, report_id, degree, is_managing_SVP, is_reporting_svp) as (
select sys_id
, sys_id
, 0
, case when title like '%SVP%' then 1 else 0 end
, case when title like '%SVP%' then 1 else 0 end
from sys_user
union all
select cur.manager_id
, nxt.sys_id
, cur.degree+1
, cur.is_managing_SVP
, case when nxt.title like '%SVP%' then 1 else 0 end
from closure cur
join sys_user nxt
on nxt.manager = cur.report_id
and nxt.sys_id <> nxt.manager
)
select r.sys_id, r.name, r.title, c.degree
, c.manager_id SVP_ID
, m.name SVP_name
, m.title SVP_title
from sys_user r
join closure c
on c.report_id = r.sys_id
join sys_user m
on m.sys_id = c.manager_id
where r.title like '%contractor%'
and c.is_managing_svp = 1
| sys_id | name | title | degree | SVP_ID | SVP_name | SVP_title |
|---|---|---|---|---|---|---|
| 555789 | Tina Belcher | Contractor | 2 | 654321 | Calvin Fischoeder | SVP |
with closure(manager_id, report_id, degree, is_managing_SVP, is_reporting_svp) as (
select sys_id
, sys_id
, 0
, case when title like '%SVP%' then 1 else 0 end
, case when title like '%SVP%' then 1 else 0 end
from sys_user
union all
select cur.manager_id
, nxt.sys_id
, cur.degree+1
, cur.is_managing_SVP
, case when nxt.title like '%SVP%' then 1 else 0 end
from closure cur
join sys_user nxt
on nxt.manager = cur.report_id
and nxt.sys_id <> nxt.manager
)
select m.sys_id manager_id
, m.name
, m.title
, c.degree
, r.sys_id report_id
, r.name report_name
, r.title report_title
from sys_user m
join closure c
on c.manager_id = m.sys_id
join sys_user r
on r.sys_id = c.report_id
where m.name = 'Calvin Fischoeder'
order by degree, report_name
| manager_id | name | title | degree | report_id | report_name | report_title |
|---|---|---|---|---|---|---|
| 654321 | Calvin Fischoeder | SVP | 0 | 654321 | Calvin Fischoeder | SVP |
| 654321 | Calvin Fischoeder | SVP | 1 | 123456 | Bob Belcher | Manager |
| 654321 | Calvin Fischoeder | SVP | 2 | 555789 | Tina Belcher | Contractor |