SQL 2005 - Общее табличное выражение - Найти последним в иерархии

Допустим, у меня есть следующая таблица:

CREATE TABLE Employees
(
EmployeeId int PRIMARY KEY NOT NULL,
ParentEmployeId int REFERENCES Employees(EmployeeId) NULL,
Name varChar(255)
)

Все записи имеют первичный идентификатор, и записи могут идентифицировать другую запись как родительскую. (Моя фактическая схема не касается сотрудников, это просто упрощенная версия для иллюстрации, поэтому, если у вас есть лучший способ обработки информации о сотрудниках, он не имеет отношения к этому разговору.)

Следующие записи вставлены :

INSERT INTO Employees VALUES (1, NULL, 'Company President 1')
INSERT INTO Employees VALUES (2, NULL, 'Company President 2')

INSERT INTO Employees VALUES (3, 1, 'Company President 1 - VP')
INSERT INTO Employees VALUES (4, 2, 'Company President 2 - VP')

INSERT INTO Employees VALUES (5, 3, 'Company President 1 - VP - Secretary')
INSERT INTO Employees VALUES (6, 4, 'Company President 2 - VP - Secretary')

INSERT INTO Employees VALUES (7, 5, 'Company President 1 - VP - Secretary - Sandwich Delivery')

Эти вставки представляют:

Company President 1
    Company President 1 - VP
        Company President 1 - VP - Secretary
            Company President 1 - VP - Secretary - Sandwich Delivery
Company President 2
    Company President 2 - VP
        Company President 2 - VP - Secretary

То, что я пытаюсь сделать, это для всех сотрудников, имеющих NULL ParentEmployeeId . Я хочу найти последнего человека в цепочке, который в этом примере будет « Президент компании 1 - Вице-президент - Секретарь - Доставка сэндвичей » и « Президент компании 2 - Вице-президент - Секретарь ».

I ' У меня есть следующий CTE, который дает мне все, включая уровень вложенности, но я не уверен, что делать дальше. Я бы хотел по возможности избегать курсоров.

Кроме того, и это очень важно , у меня есть логика в другом месте, которая гарантирует, что у сотрудника может быть только 1 прямой подчиненный. Таким образом, хотя схема технически позволяет это, Президент компании 1 никогда не будет иметь в списке двух вице-президентов.

WITH EmployeeRec(EmployeeId, ParentEmployeeId, Name, Level) AS
(
    SELECT
        EmployeeId,
        ParentEmployeId,
        Name,
        1 as [Level]
    FROM
        Employees
    WHERE
        ParentEmployeId IS NULL

    UNION ALL

    SELECT
        E.EmployeeId,
        E.ParentEmployeId,
        E.Name,
        R.[Level] + 1
    FROM
        Employees E
    INNER JOIN
        EmployeeRec R
    ON
        E.ParentEmployeId = R.EmployeeId
)
SELECT * FROM EmployeeRec