SQL Server : CTE going a level back in the where clause

Question

I would like to set a limit to a recursive query. But I don't want to lose the last entry on which i limited the recursion.

Right now the query looks like this:

WITH MyCTE (EmployeeID, FirstName, LastName, ManagerID, level, SPECIAL)
AS ( 
    SELECT a.EmployeeID, a.FirstName, a.LastName, a.ManagerID, 0 as Level
    FROM MyEmployees as a
    WHERE ManagerID IS NULL

    UNION ALL
    SELECT b.EmployeeID, b.FirstName, b.LastName, b.ManagerID, level + 1
    FROM MyEmployees as b
    INNER JOIN MyCTE ON b.ManagerID = MyCTE.EmployeeID
    WHERE b.ManagerID IS NOT NULL and b.LastName != 'Welcker' 
)
SELECT *
FROM MyCTE
OPTION (MAXRECURSION 25) 

And it gets limited by the Manager Welcker, the result looks like this:

EmployeeID; FirstName; LastName; ManagerID; level
1           Ken        Sánchez   NULL       0

What I want is to have the employee 'Welcker' included. The problem is I only have the name of the last person I need to have on my list. There are some entries below in the command structure but I don't know them and I don't want to see them.

Here's a example of how I imagine the result of my query to look.

EmployeeID FirstName LastName ManagerID level
1          Ken       Sánchez  NULL        0
273        Brian     Welcker  1           1

Any help would be very much appreciated

Answer 1

WITH MyCTE (EmployeeID, FirstName, LastName, ManagerID, level, NotMatched)
AS ( 
    SELECT a.EmployeeID, a.FirstName, a.LastName, a.ManagerID, 0 as Level, 0 AS NotMatched
    FROM MyEmployees as a
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT b.EmployeeID, b.FirstName, b.LastName, b.ManagerID, level + 1,
           CASE WHEN (MyCTE.LastName = 'Welcker' AND MyCTE.level = 1) THEN 1 ELSE MyCTE.NotMatched END
    FROM MyEmployees as b
    INNER JOIN MyCTE ON b.ManagerID = MyCTE.EmployeeID
)
SELECT *
FROM MyCTE
WHERE NotMatched != 1