Employee Hierarchy Recursive CTE Different Top Level Directors Overtime

  • Hi,

    I have looked at some examples of Employee > Manager Hierarchy using Recursive CTE.

    The issue I have is the Top of Chain i.e. the Chairman there was different Chairman's over a period of time for example:

     

    Final Results I require

     

    If you look at EmpId 129 Rafael Nadal he left at the time Jeff Bezos was Chairman but I am not sure how to write the SQL for this?

     

    Thanks

  • If you want someone to attempt a coded solution, please provide the sample data in a consumable format. With > 3,000 points, I'm sure you know what that means.

    Also, it seems that you already know how to write the rCTE where there is only one top-level (ManagerId = 0) row, so it would be helpful to see what you have done so far.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • In other words, please read and heed the article at the first link in my signature line below for one of many methods to provide some "Readily Consumable Test Data" to help us help you.  It won't take you as long as it would take use because we'd have to copy from your graphic.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  •  

    Sorry I forgot to include the the Table and Inserts and SQL I currently have, here it is below:

     

    CREATE TABLE [dbo].[EmployeeList](
    [EmpId] [int] NOT NULL,
    [ManagerId] [int] NULL,
    [Name] [varchar](100) NULL,
    [JobTitle] [varchar](100) NULL,
    [ValidFrom] [datetime] NULL,
    [ValidTo] [datetime] NULL
    ) ON [PRIMARY]
    GO

    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (100, 0, N'Bill Gates', N'Chairman', CAST(0x00008EAC00000000 AS DateTime), CAST(0x0000AA5F018B80D4 AS DateTime))
    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (101, 0, N'Jeff Bezos', N'Chairman', CAST(0x0000AA6000000000 AS DateTime), CAST(0x0000AE10018B80D4 AS DateTime))
    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (100, 0, N'Bill Gates', N'Chairman', CAST(0x0000AE1100000000 AS DateTime), CAST(0x00016BD0018B80D4 AS DateTime))
    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (107, 109, N'Rick James', N'Senior Developer', CAST(0x000097E600000000 AS DateTime), CAST(0x00016BD0018B80D4 AS DateTime))
    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (117, 109, N'Tom Cruise', N'Senior Developer', CAST(0x00009F0A00000000 AS DateTime), CAST(0x00016BD0018B80D4 AS DateTime))
    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (109, 100, N'Roger Federer', N'Manager', CAST(0x0000AE3900000000 AS DateTime), CAST(0x00016BD0018B80D4 AS DateTime))
    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (118, 107, N'Novak Djokovic', N'Analyst', CAST(0x0000AEBC00000000 AS DateTime), CAST(0x00016BD0018B80D4 AS DateTime))
    INSERT [dbo].[EmployeeList] ([EmpId], [ManagerId], [Name], [JobTitle], [ValidFrom], [ValidTo]) VALUES (129, 109, N'Rafael Nadal', N'Analyst', CAST(0x0000A72E00000000 AS DateTime), CAST(0x0000ACA3018B80D4 AS DateTime))

     

    This is the SQL I have but I am getting Duplicate lines:

     

    DROP TABLE #tmp;

    WITH EmployeeList AS (
    SELECT [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    FROM [SCHStage].[dbo].[EmployeeList]
    )


    Select * into #tmp
    From EmployeeList;


    WITH DirectorList AS
    (
    SELECT [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    FROM #tmp a
    WHERE
    a.ManagerID = 0


    ),


    NewList AS
    (
    SELECT
    [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    FROM #tmp a
    ),


    cteclass AS
    (
    SELECT
    [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    ,1 AS Level
    ,CAST ((Name) AS VARCHAR (max)) Hierarchy
    FROM DirectorList
    UNION
    ALL
    SELECT
    e.[EmpId]
    ,e.[ManagerId]
    ,e.[Name]
    ,e.[JobTitle]
    ,e.[ValidFrom]
    ,e.[ValidTo]
    ,LEVEL + 1
    ,o.Hierarchy + ' / ' + cast (e.Name AS varchar (max))
    FROM NewList e
    JOIN cteclass o
    ON o.EmpId = e.ManagerId
    ),

    FHierarchy AS
    (
    SELECT *, CHARINDEX ('/', hierarchy, 1) Split
    FROM cteclass
    )

    SELECT
    EmpId
    ,Name
    ,ManagerId
    ,CASE WHEN Level = 1 THEN Name ELSE REPLACE (SUBSTRING (Hierarchy, 1, Split), ' /', '') END Director
    ,Hierarchy
    FROM
    FHierarchy
    ORDER BY 2

     

  • I have included the dates to the join but it is now not showing EmpId 129 (Rafael Nadal)

    DROP TABLE #tmp;

    WITH EmployeeList AS (
    SELECT [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    FROM [SCHStage].[dbo].[EmployeeList]
    )


    Select * into #tmp
    From EmployeeList;


    WITH DirectorList AS
    (
    SELECT [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    FROM #tmp a
    WHERE
    a.ManagerID = 0


    ),


    NewList AS
    (
    SELECT
    [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    FROM #tmp a
    ),


    cteclass AS
    (
    SELECT
    [EmpId]
    ,[ManagerId]
    ,[Name]
    ,[JobTitle]
    ,[ValidFrom]
    ,[ValidTo]
    ,1 AS Level
    ,CAST ((Name) AS VARCHAR (max)) Hierarchy
    FROM DirectorList
    UNION
    ALL
    SELECT
    e.[EmpId]
    ,e.[ManagerId]
    ,e.[Name]
    ,e.[JobTitle]
    ,e.[ValidFrom]
    ,e.[ValidTo]
    ,LEVEL + 1
    ,o.Hierarchy + ' / ' + cast (e.Name AS varchar (max))
    FROM NewList e
    JOIN cteclass o
    ON o.EmpId = e.ManagerId
    AND e.[ValidFrom] <= o.ValidTo
    AND e.ValidTo >= o.ValidFrom
    ),

    FHierarchy AS
    (
    SELECT *, CHARINDEX ('/', hierarchy, 1) Split
    FROM cteclass
    )

    SELECT
    EmpId
    ,Name
    ,ManagerId
    ,CASE WHEN Level = 1 THEN Name ELSE REPLACE (SUBSTRING (Hierarchy, 1, Split), ' /', '') END Director
    ,Hierarchy
    FROM
    FHierarchy
    ORDER BY 1

    • This reply was modified 1 month, 1 week ago by  SQL_Kills.
  • >>If you look at EmpId 129 Rafael Nadal he left at the time Jeff Bezos was Chairman but I am not sure how to write the SQL for this?

    Obviously you you not have enough data for at least Roger Federer as you only have the row valid from 2022-02-11.

    Is EmployeeList a versioned table? If it is you will need to look at the historical data with something like:

    SELECT *
    FROM dbo.EmployeeList FOR SYSTEM_TIME ALL;

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply