Recursive SQL with CTE

 Build hierarchy using CTE and recursion

http://msdn.microsoft.com/en-us/library/ms186243.aspx

 Here is a query that uses a CTE to build an employee hierarchy and will display how many levels of employees are below a manager. 

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ManagerID, HierarchyLevel) AS
(
   -- Base case
   SELECT
      EmployeeID,
      LastName,
      FirstName,
      ManagerID,
      0 as HierarchyLevel
   FROM dbo.Employees
   WHERE RecordEnd is null
 AND EmployeeID NOT IN (SELECT DISTINCT ManagerID FROM dbo.Employees WHERE RecordEnd is null)

   UNION ALL

   -- Recursive step
   SELECT
     e.EmployeeID,
      e.LastName,
      e.FirstName,
      e.ManagerID,
      eh.HierarchyLevel + 1 AS HierarchyLevel
   FROM dbo.Employees e
      INNER JOIN EmployeeHierarchy eh ON
         e.EmployeeID = eh.ManagerID and e.ManagerID <> -1
 Where e.RecordEnd is null
)

SELECT DISTINCT eh.EmployeeID,
      eh.LastName,
      eh.FirstName,
      eh.ManagerID,
     eh.HierarchyLevel
FROM  EmployeeHierarchy EH
INNER JOIN (SELECT  employeeid, MAX(HierarchyLevel) AS 'Level'
                        FROM EmployeeHierarchy group by employeeid) MaxLevel
ON EH.EmployeeID=MaxLevel.EmployeeID AND EH.HierarchyLevel=MaxLevel.Level
ORDER BY lastname, firstname