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) -
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 -
>>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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply