Selecting a VP from an organizational hierarchy

• J.O.

  Right there with Babe

  Points: 756

  More actions

  August 12, 2018 at 8:00 pm

  #377474

  Hello SQL Gurus!
  I currently have a CTE that if I plug in the VP, it will show all the employees that roll up to that VP. I need to figure out how to do the opposite and return a VP (Vice president), that an employee rolls up to.

  Using the sample data below, when I query the data it would need to return the following: (note, the VP will have VP in the title so that is where I know where to stop)

  EmployeeName          Title                                            VPName
  Bart Simpson             Developer                                    Monty Burns
  Marge  Simpson         Manager of Developers                 Monty Burns
  Homer Simpson          Director of Developers                   Monty Burns
  Chris Griffin                 Engineer                                     Carter Pewterschmidt
  Lois Griffin                   Manager of Engineers                  Carter Pewterschmidt
  Peter Griffin                  Director or Engineers                  Carter Pewterschmidt

  Hope this is clear and thank you in advance!

  
CREATE TABLE #Employees(
             [EmployeeID] INT IDENTITY(1,1) NOT NULL,
             [PersonnelNumber]                   [int] NULL,
             [EmployeeName]                                        [nvarchar](128) NULL,
             [ReportsToPersonnelNumber]               [int] NULL,
             [Title]                                                      [nvarchar](128) NULL,
            
       CONSTRAINT [tempPK_EmployeeID] PRIMARY KEY CLUSTERED
       (
             [EmployeeID] ASC
       )
       )
 
INSERT INTO #Employees
(
    --EmployeeID - this column value is auto-generated
       PersonnelNumber,
    EmployeeName,
    ReportsToPersonnelNumber,
    Title
)
SELECT 1234, 'Bart Simpson', 12345 , 'Developer'
UNION ALL
SELECT 12345, 'Marge Simpson', 123456, 'Manager of Developers'
UNION ALL
SELECT 123456, 'Homer Simpson', 1234567, 'Director of Developers'
UNION ALL
SELECT 1234567, 'Monty Burns', 1, 'VP of Developers'
UNION ALL
SELECT 1, 'Bill Gates', NULL, 'CEO'
UNION ALL
SELECT 4321, 'Chris Griffin', 54321 , 'Engineer'
UNION ALL
SELECT 54321, 'Lois Griffin', 654321, 'Manager of Engineers'
UNION ALL
SELECT 654321, 'Peter Griffin', 7654321, 'Director of Engineers'
UNION ALL
SELECT 7654321, 'Carter Pewterschmidt', 1, 'VP of Engineers'
 
SELECT * FROM #Employees e
 
DROP TABLE #Employees

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  August 12, 2018 at 11:45 pm

  #2001422

  Here is one suggestion, uses a recursive CTE
  😎

  
  USE TEEST;
GO
SET NOCOUNT ON;

  IF OBJECT_ID(N'tempdb..#Employees') IS NOT NULL DROP TABLE #Employees;
CREATE TABLE #Employees
(
  EmployeeID      INT IDENTITY(1,1) NOT NULL  CONSTRAINT tempPK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
  ,PersonnelNumber    int       NULL
  ,EmployeeName     nvarchar(128)    NULL
  ,ReportsToPersonnelNumber int       NULL
  ,Title        nvarchar(128)    NULL
);

  INSERT INTO #Employees
(
  PersonnelNumber
 ,EmployeeName
 ,ReportsToPersonnelNumber
 ,Title
)
VALUES
( 1234, 'Bart Simpson'   , 12345, 'Developer'     )
,( 12345, 'Marge Simpson'   , 123456, 'Manager of Developers' )
,( 123456, 'Homer Simpson'   , 1234567, 'Director of Developers')
,(1234567, 'Monty Burns'   ,   1, 'VP of Developers'  )
,(  1, 'Bill Gates'    ,  NULL, 'CEO'       )
,( 4321, 'Chris Griffin'   , 54321 , 'Engineer'     )
,( 54321, 'Lois Griffin'   , 654321, 'Manager of Engineers' )
,( 654321, 'Peter Griffin'   , 7654321, 'Director of Engineers' )
,(7654321, 'Carter Pewterschmidt',   1, 'VP of Engineers'   )

  DECLARE @EMP_ID INT = 1; -- Bart Simpson

  ;WITH BASE_DATA AS
(
  SELECT
   E.EmployeeID
   ,E.PersonnelNumber
   ,E.EmployeeName
   ,E.Title
   ,E.ReportsToPersonnelNumber
  FROM  #Employees E
  WHERE E.EmployeeID  = @EMP_ID
UNION ALL
  SELECT
   EE.EmployeeID
   ,EE.PersonnelNumber
   ,EE.EmployeeName
   ,EE.Title
   ,EE.ReportsToPersonnelNumber
  FROM BASE_DATA          BD
  INNER JOIN #Employees        EE
  ON    BD.ReportsToPersonnelNumber = EE.PersonnelNumber
)
SELECT
  BD.EmployeeID
  ,BD.PersonnelNumber
  ,BD.EmployeeName
  ,BD.Title
FROM  BASE_DATA BD
WHERE BD.Title  LIKE  N'VP %';