MS SQL Sever: Creating a Supervisory Hierarchy: Issues Joining Many Tables

Question

I have to create a report which shows the supervisory relationship hierarchy at an organization. There are many different ways a supervisory line can be set up. Below are some of them.

Vice President-Associate VP-Director-Assistant Director-Supervisor-worker
Vice President-Associate VP-Director-Supervisor - worker
Vice President-Director-Assistant Director-Supervisor-worker
Vice President-Director-worker
Vice President-Director-Director B-Worker
Vice President-Director-Director B-Supervisor->Worker
Vice President-Associate VP-worker
Vice President-worker
Vice President-Director-Assistant Director
Vice President-Director-worker

These is a table for each role in the hierarchy; so, a VP table, AVP table, Director table and so on. If the hierarchy line does not contain a certain role than that value should comes back null. I wrote the program so I start with the Worker table and then left join all the tables from there. I have tried to factor in all the possible joins (see program below). I am using different join combinations using Supervisor_Position_NBR=POSITION_NBR There are many different ways the the lines look like back here is a small example:

Vice President Associate_VP Director Director B Assistant_Director Supervisor Worker ============== ============ ======== ========== ================== ========== ====== Jane Smith Joe Roberts Marcy James Null Null James Mare Matt G
Jane Smith Joe Roberts Marcy James Null Null James Mare Jess D Jane Smith Joe Roberts Marcy James Null Null Frank jay Carol R Jane Smith Null Marty Bob Null Sonja Null Sam Smith Jane Smith Null Marty Bob Null Sonja Null Nate lowe Jane Smith Null Null Null Null Null Ralph Cole

My two issues are:

When I have a supervisory line which includes a VP , Director and Worker but the both the director and the worker report directly to a VP, when the query is run the Director drops off and only the worker shows. So this:

Vice President Associate_VP Director Director B Assistant Director Supervisor Worker
============== =========== ======= ========== ================= ========= ======= Jane Smith Null Null Null Null Null MarcyJames

When I should bring back this:

Vice President Associate_VP Director Director B Assistant Director Supervisor Worker
============= =========== ======== ========== ======== ======= ========= ===== Jane Smith Null Null Null Null Null Marcy James
Jane Smith Null Joe Roberts Null Null Null Null

Is there a cleaner more efficient way to write this query?

Any assistance is greatly appreciated.

select
    vp.Vice_President,
    avp.Associate_VP,  
    d.director, 
    db.director_B,
    ad.Assistant_Director,
    s.Supervisor,
    w.worker
    from gw_ppp.dbo.vw_worker w 
    left join gw_ppp.dbo.vw_Manager_Sup_Role s 
    on w.Supervisor_Position_NBR=s.POSITION_NBR
    left join gw_ppp.dbo.vw_ADIR_Role AD 
    on w.Supervisor_Position_NBR=AD.POSITION_NBR
    or s.Supervisor_Position_NBR=AD.POSITION_NBR
    left join gw_ppp.dbo.vw_Dir_Role_B db 
    on w.Supervisor_Position_NBR=db.POSITION_NBR
    or s.Supervisor_Position_NBR=db.POSITION_NBR
    or AD.Supervisor_Position_NBR=db.POSITION_NBR
    left join gw_ppp.dbo.vw_Dir_Role D 
    on w.Supervisor_Position_NBR=D.POSITION_NBR
    or s.Supervisor_Position_NBR=D.POSITION_NBR
    or AD.Supervisor_Position_NBR=D.POSITION_NBR
    or db.Supervisor_Position_NBR=D.POSITION_NBR
    left join gw_ppp.dbo.vw_AVP_Role AVP 
    on w.Supervisor_Position_NBR=AVP.POSITION_NBR
    or s.Supervisor_Position_NBR=AVP.POSITION_NBR
    or AD.Supervisor_Position_NBR=AVP.POSITION_NBR
    or D.Supervisor_Position_NBR=AVP.POSITION_NBR
    left join  gw_ppp.dbo.vw_VP_Role VP 
    on w.Supervisor_Position_NBR=VP.POSITION_NBR
    or s.Supervisor_Position_NBR=vp.POSITION_NBR
    or ad.Supervisor_Position_NBR=vp.POSITION_NBR
    or d.Supervisor_Position_NBR=vp.POSITION_NBR
    or avp.Supervisor_Position_NBR=vp.POSITION_NBR
    order by w.worker

Here are the programs I use to create the Role views

CREATE VIEW [vw_VP_Role]
AS

SELECT 
File_NBR, 
job_title, 
(First_Name + ' ' + Last_Name) as Vice_President, 
POSITION_NBR ,
Supervisor_Position_NBR, 
(Mngr_FName + ' ' + Mngr_LName ) as vp_Manager 
FROM [New_EEs].[dbo].[ADPFile]
where Job_title in 
('Vice President','Sr VP & Chief Financial Officer','Sr. Vice President');



CREATE VIEW [vw_AVP_Role]
AS
SELECT 
File_NBR, 
job_title, 
(First_Name + ' ' + Last_Name) as Associate_VP, 
POSITION_NBR,
Supervisor_Position_NBR, 
(Mngr_FName + ' ' + Mngr_LName ) as Avp_Manager 
FROM [New_EEs].[dbo].[ADPFile]
where Job_title in 
('Associate Vice President','Senior Performance Officer');

CREATE VIEW [vw_Dir_Role]
AS
SELECT 
File_NBR, 
job_title, 
(First_Name + ' ' + Last_Name) as Director, 
POSITION_NBR,
Supervisor_Position_NBR, 
(Mngr_FName + ' ' + Mngr_LName ) as Dir_Manager 
FROM [New_EEs].[dbo].[ADPFile]
where Job_title in
('Director','Chief Information Officer','Deputy Controller','Director of Operations & Staff Dev')
and not (First_Name + ' ' + Last_Name) in ('Michelle James','Edward Lachterman', 'Nafissa Hannat')

CREATE VIEW [vw_Dir_Role_B]
AS
SELECT 
File_NBR, 
job_title, 
(First_Name + ' ' + Last_Name) as Director_B, 
POSITION_NBR,
Supervisor_Position_NBR, 
(Mngr_FName + ' ' + Mngr_LName ) as Dir_Manager 
FROM [New_EEs].[dbo].[vw_ADPFile]
where (First_Name + ' ' + Last_Name) in ('Michelle James','Edward Lachterman', 'Nafissa Hannat')

CREATE VIEW [vw_ADIR_Role]
AS
SELECT 
File_NBR, 
job_title, 
(First_Name + ' ' + Last_Name) as Assistant_Director, 
POSITION_NBR,
Supervisor_Position_NBR, 
(Mngr_FName + ' ' + Mngr_LName ) as AD_Manager 
FROM [New_EEs].[dbo].[ADPFile]
where Job_title= 'Assistant Director'

CREATE VIEW [vw_Manager_Sup_Role]
AS 
SELECT 
File_NBR, 
job_title, 
(First_Name + ' ' + Last_Name) as Supervisor, 
POSITION_NBR,
Supervisor_Position_NBR, 
(Mngr_FName + ' ' + Mngr_LName ) as Sup_Manager 
FROM [New_EEs].[dbo].[vw_ADPFile]
where Job_title in ('Supervisor','Campus Administration Manager','Compensation & Benefits Manager',
'Cottage Manager', 'Manager','Office Manager','Operations Manager','Recruiting Manager','Special Projects/Rep & Compliance Manager', 'Talent Manager','Youth Development Coordinator')  or (First_Name + ' ' + Last_Name)='Rosa Nunez Pena';

CREATE VIEW [vw_worker]
 as
SELECT 
File_NBR, 
job_title, 
(First_Name + ' ' + Last_Name) as worker, 
POSITION_NBR,
Supervisor_Position_NBR, 
(Mngr_FName + ' ' + Mngr_LName ) as worker_Manager 
  FROM [New_EEs].[dbo].[ADPFile]
  where Job_title not in 
  ('Associate Vice President','Vice President','Sr VP & Chief Financial Officer','Sr. Vice President', 'Director','Chief Information Officer','Deputy Controller','Director of Operations & Staff Dev', 'Assistant Director','Supervisor','Campus Administration Manager','Compensation & Benefits Manager', 'Cottage Manager', 'Manager','Office Manager','Operations Manager','Recruiting Manager','Special Projects/Rep & Compliance Manager', 'Talent Manager','Youth Development Coordinator','Senior Performance Officer')  and  not  (First_Name + ' ' + Last_Name)='Rosa Nunez Pena';

Take a look at this post http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — , Jul 19 '13 at 17:57
Thanks. I don't have the permissions to alter/create tables so, it becomes more challenging. I will read through the paper. — user2270911, Jul 19 '13 at 18:01
You really have a table for each role? Or do those views all access the same table? — HLGEM, Jul 19 '13 at 19:16
I assume POSITION_NBR is unique? So we aren't going to see an assistant director that has the same POSITION_NBR as a VP? — dazedandconfused, Jul 19 '13 at 20:42
@dazedandconfused Yes, unique. you will not see an assistant director that has the same POSITION_NBR as a VP — user2270911, Jul 19 '13 at 21:07
Then don;t use the views, use a recursive cte that access the orginal table — HLGEM, Jul 19 '13 at 21:08
@HLGEM I would but I don't know how to write the program otherwise because its impossible to define the roles unless sperate views are made. What is "recursive cte"? — user2270911, Jul 19 '13 at 21:13
Read Books online to understand what a Recusive CTE does but basically it build a hierarchy. Without your table structure it would be hard to help you here. — HLGEM, Jul 19 '13 at 21:17
@HLGEM I added in above the programs which create the separate role views — user2270911, Jul 19 '13 at 21:29
The AFPFILE table structure is just the columns included in the view programs above — user2270911, Jul 19 '13 at 22:34

score 0 · Answer 1 · answered Jul 20 '13 at 01:30

Consider a Common Table Expression like the following...

WITH org AS 
(
    SELECT 
        ee.POSITION_NBR, ee.EmpName, ee.Title
    FROM 
        employee AS ee
    UNION ALL 
    SELECT ee.POSITION_NBR, o.EmpName, o.Title
    FROM 
    employee AS ee 
    JOIN org o ON (o.POSITION_NBR = ee.SupervisorId)
)

SELECT * FROM org WHERE (POSITION_NBR IN (SELECT POSITION_NBR FROM employee where title = 'Worker'))

For each worker this recursive CTE retrieves all of the employees that are in the worker's management chain.

You can then take this and PIVOT it, to get the format you want. Missing levels of management will automatically have a NULL value in them.

Look at this SQL Fiddle for a full example. For brevity, I only included the first three levels of management but you should be able to follow this pattern to add the others.

This is great! Thanks! I have a question though. What if I have a line that a VP has a director and a worker reporting to them but the worker is not under the director and reports directly to the VP. — user2270911, Jul 20 '13 at 03:33
Look at the Fiddle example. Notice that William does not report to a supervisor, but reports straight to a Director. The query still shows it correctly. — dazedandconfused, Jul 20 '13 at 03:50

MS SQL Sever: Creating a Supervisory Hierarchy: Issues Joining Many Tables

1 Answers1