Employee Hierarchy Recursive CTE Different Top Level Directors Overtime

Question

Employee Hierarchy Recursive CTE Different Top Level Directors Overtime

SQL_Kills

Hall of Fame

Points: 3154
More actions
July 3, 2022 at 10:24 pm

#4058264

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 2

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)

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 3

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

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 4

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 2 years, 4 months ago by SQL_Kills.

Ken McKelvey SSCoach Points: 18842 More actions · Answer 5

>>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;