Solution 4, Sql Server

Solution 4

/*
Problem:
    Write a SQL statement to show an employee management reporting order starting with Emily Theron.
    Add your solution at the end.

Expected Output:
    EmployeeId	FirstName	LastName
    10			Emily		Theron
    7			Olivia		Johansson
    5			Jacob		Foxx
    2			Liam		Brolin
    1			Noah		Clooney
*/

-- Create a table called Employees
CREATE TABLE Employees (
    EmployeeId         INT     PRIMARY KEY,
    FirstName          VARCHAR(200),
    LastName           VARCHAR(200),
    ManagerEmployeeId  INT)
GO

-- Fill Employee table
INSERT INTO Employees VALUES(1, 'Noah', 'Clooney', NULL)
INSERT INTO Employees VALUES(2, 'Liam', 'Brolin', 1)
INSERT INTO Employees VALUES(3, 'Mason', 'Farrell', 1)
INSERT INTO Employees VALUES(4, 'Emma', 'Jolie', 1)
INSERT INTO Employees VALUES(5, 'Jacob', 'Foxx', 2)
INSERT INTO Employees VALUES(6, 'William', 'Damon', 4)
INSERT INTO Employees VALUES(7, 'Olivia', 'Johansson', 5)
INSERT INTO Employees VALUES(8, 'Isabella', 'Portman', 1)
INSERT INTO Employees VALUES(9, 'Ethan', 'Firth', 6)
INSERT INTO Employees VALUES(10, 'Emily', 'Theron', 7)
GO

------------------------------------------------------------------
-- Sql Server 2014 Express Edition - 12.0.2000.8
-- Solution.
------------------------------------------------------------------

DECLARE @listOfIDs VARCHAR(1024);

WITH hierarchycte(id, ManagerEmployeeId, LEVEL, treepath) AS
( SELECT EmployeeId AS id, ManagerEmployeeId, 0 AS LEVEL,
CAST(EmployeeId AS VARCHAR(1024)) AS treepath
FROM Employees
WHERE ManagerEmployeeId IS NULL
UNION ALL -- and now for the recursive part 
SELECT e.EmployeeId AS id, e.ManagerEmployeeId,
hierarchycte.LEVEL + 1 AS LEVEL,
CAST(hierarchycte.treepath + ',' +
CAST(e.EmployeeId AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath
FROM Employees e
INNER JOIN hierarchycte
ON hierarchycte.id = e.ManagerEmployeeId)
SELECT @listOfIDs = treepath
FROM hierarchycte
where id = 10
ORDER BY treepath

DECLARE @SQLQuery AS NVARCHAR(500)
SET @SqlQuery = '
SELECT EmployeeId, FirstName, LastName
FROM Employees
WHERE EmployeeId IN (' +  @listOfIDs + ')
ORDER BY EmployeeId DESC'
EXECUTE(@SQLQuery)


run \| edit \| history \| help	0

Please log in to post a comment.