Write a recursive CTE that will get employees by their manager. Include the following columns:

• Employee LastName
• Employee FirstName
• Department ID
• FileFolder
• Manager LastName
• Manager FirstName

The column called FileFolder is used to store the performance review for each employee. Note since managers will also have access to all the people that report to them either directly or indirectly, each manager’s folder will eventually be setup to contain not just their own performance review files, but also all the subfolders for each of the employees that report directly to them. To help facilitate that, also include a column called “File Path” that will determine and show the file path name for each employee using Windows style of \ between subfolders, ie. in the format ManagerFileFolder\EmployeeFileFolder\ etc.

To illustrate how this would work, if for example I reported directly to Dev Sainani and Dev reports to Peter Devlin, then my File Path would be PeterDevlin\DevSainani\OsamAl

I am not sure how I am to include columns that are not in the database table I am provided without altering the table and how to incorporate the "File Path" requirements mentioned above.

This is the script for the Employees table (the only table I'm provided):

CREATE TABLE dbo.Employees 
(
    EmployeeID        INT IDENTITY PRIMARY KEY,
    DepartmentID      INT 
        CONSTRAINT FK_Employee_Department 
            FOREIGN KEY REFERENCES dbo.Departments (DepartmentID),
    ManagerEmployeeID INT 
        CONSTRAINT FK_Employee_Manager 
            FOREIGN KEY REFERENCES dbo.Employees (EmployeeID),
    FirstName         NVARCHAR(60),
    LastName          NVARCHAR(60),
    Salary            MONEY 
        CONSTRAINT CK_EmployeeSalary CHECK (Salary >= 0),
    CommissionBonus   MONEY 
        CONSTRAINT CK_EmployeeCommission CHECK (CommissionBonus >= 0),
    FileFolder        NVARCHAR(256) 
        CONSTRAINT DF_FileFolder DEFAULT 'ToBeCreated'
);

This is what I have done, I know its not correct because I'm not getting any data shown in the table when I write a SELECT statement for this CTE:

WITH GetEmployeeByManager AS 
(
    SELECT 
        FirstName, LastName, DepartmentID, FileFolder
    FROM 
        dbo.Employees
    WHERE 
        ManagerEmployeeID IS NULL

    UNION ALL

    SELECT 
        e.LastName, e.FirstName, e.DepartmentID, e.FileFolder
    FROM 
        Employees e
    JOIN 
        GetEmployeeByManager ge ON e.ManagerEmployeeID = ge.ManagerEmployeeID
)
SELECT *
FROM GetEmployeeByManager ge
JOIN dbo.Employees e ON ge.ManagerEmployeeID = e.ManagerEmployeeID;

CodePudding user response：

This script gets you the desired output you describe. In the CREATE table statement, I removed the foreign key constraints for simplicity as I was using SQL Fiddle to construct this query.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE Employees 
(
    EmployeeID        INT not null PRIMARY KEY,
    DepartmentID      INT not null,
    ManagerEmployeeID INT null,
    FirstName         NVARCHAR(60),
    LastName          NVARCHAR(60),
    Salary            MONEY 
        CONSTRAINT CK_EmployeeSalary CHECK (Salary >= 0),
    CommissionBonus   MONEY 
        CONSTRAINT CK_EmployeeCommission CHECK (CommissionBonus >= 0),
    FileFolder        NVARCHAR(256) 
        CONSTRAINT DF_FileFolder DEFAULT 'ToBeCreated'
);

INSERT INTO Employees (EmployeeID, DepartmentID, ManagerEmployeeID, FirstName, LastName, Salary, CommissionBonus, FileFolder)
VALUES (1, 101, null, 'Ted', 'Smith', 12000.00, 120.00, 'TedSmith')
  , (2, 101, 1, 'John','Doe', 10000.00, 100.00, 'JohnDoe')
  , (3, 101, 2, 'Dev', 'Patel', 8000.00, 80.00, 'DevPatel')
;

Query 1:

WITH GetEmployeeByManager AS 
(
    SELECT 
        e.EmployeeID
        , e.FirstName
        , e.LastName
        , e.DepartmentID
        , e.ManagerEmployeeID
        , em.FirstName as ManagerFirstName
        , em.LastName as ManagerLastName
        , e.FileFolder
        , e.FileFolder as FilePath
        , 0 as hierarchy_level
    FROM Employees as e
        LEFT OUTER JOIN Employees as em
            ON em.EmployeeID = e.ManagerEmployeeID
    WHERE e.ManagerEmployeeID is null --First query gets only managers

    UNION ALL

    SELECT 
        e.EmployeeID
        , e.FirstName
        , e.LastName
        , e.DepartmentID
        , e.ManagerEmployeeID
        , em.FirstName as ManagerFirstName
        , em.LastName as ManagerLastName
        , e.FileFolder
        , CAST(em.FilePath   '/'   e.FileFolder as nvarchar(256)) as FileFolder
        , em.hierarchy_level   1 as hierarchy_level
    FROM Employees e
        INNER JOIN GetEmployeeByManager as em
            ON em.EmployeeID = e.ManagerEmployeeID
    WHERE em.hierarchy_level < 50
)
SELECT *
FROM GetEmployeeByManager ge

Results:

| EmployeeID | FirstName | LastName | DepartmentID | ManagerEmployeeID | ManagerFirstName | ManagerLastName | FileFolder |                  FilePath | hierarchy_level |
|------------|-----------|----------|--------------|-------------------|------------------|-----------------|------------|---------------------------|-----------------|
|          1 |       Ted |    Smith |          101 |            (null) |           (null) |          (null) |   TedSmith |                  TedSmith |               0 |
|          2 |      John |      Doe |          101 |                 1 |              Ted |           Smith |    JohnDoe |          TedSmith/JohnDoe |               1 |
|          3 |       Dev |    Patel |          101 |                 2 |             John |             Doe |   DevPatel | TedSmith/JohnDoe/DevPatel |               2 |