WITH                                                   
CTE_Employee AS
(
-- This SELECT statement returns the anchor element of the recursive query. The 
-- anchor element is the top level Employee of Adventure Works, the CEO.
   SELECT
       [EmployeeKey]
      ,[FirstName]
      ,[LastName]
      ,[Title]
      ,[ParentEmployeeKey]
      ,[VacationHours]
      ,[SickLeaveHours]
-- >> The field Level is used to calculate the hierarchy level of an employee. 
--    The CEO is on Level 1. All top level managers are on Level 2. 
--    Regional Managers, Technical supervisors etc. are on Level 3
      ,1 AS [Level]
   FROM
      [dbo].[DimEmployee]
   WHERE
      [ParentEmployeeKey] IS NULL
   -- >> The operator UNION ALL is the only allowed operator allowed between the 
   --    anchor and the the first recursive member.
   UNION ALL
   SELECT
       T01.[EmployeeKey]
      ,T01.[FirstName]
      ,T01.[LastName]
      ,T01.[Title]
      ,T01.[ParentEmployeeKey]
      ,T01.[VacationHours]
      ,T01.[SickLeaveHours]
--  >> Increases the level for each recursion
      ,T02.[Level] + 1 AS [Level]
    FROM
       [dbo].[DimEmployee] T01
       INNER JOIN CTE_Employee T02
       ON
         T01.[ParentEmployeeKey] = T02.[EmployeeKey]
)
/* # If the recursive member query definition returns the same values for both
     the parent and child columns, an infinite loop is created. To avoid an 
     infinite  loop you can limit the number of recursions. By default SQL 
     Server limits the recursions to 100. The maximium numbver is limited to 
     32767 recursions. 
   # The number of recursions can be limited with the option MACRECURSION
   # If the number of recursion exceeds the specified value for MACRECURSION
     SQL Server will throw an exception
   # The option cannot be used within a CTE */
SELECT
    [EmployeeKey]
   ,[FirstName]
   ,[LastName]
   ,[Title]
   ,[ParentEmployeeKey]
   ,[Level]
   ,[VacationHours]
   ,[SickLeaveHours]
-- >> Classifies the vacation hours by Level (3 levels)
   ,NTILE(3)
       OVER (PARTITION BY [Level]
                 ORDER BY [VacationHours]
            ) AS [VacationHours_NTILE]
-- >> Orders the vacation hours by Level
   ,DENSE_RANK()
       OVER (PARTITION BY [Level]
                 ORDER BY [VacationHours]
            ) AS [VacationHours_DENSE_RANK]
-- >> Classifies the sick leave hours by Level (3 levels)
   ,NTILE(3)
       OVER (PARTITION BY [Level]
                 ORDER BY [SickLeaveHours]
            ) AS [SickLeaveHours_NTILE]
-- >> Orders the sick leave hours hours by Level
   ,DENSE_RANK()
       OVER (PARTITION BY [Level]
                 ORDER BY [SickLeaveHours]
            ) AS [SickLeaveHours_DENSE_RANK]
FROM
   CTE_Employee
-- >> Limits the maximum number of recursions to 5 recursions
-- OPTION (MAXRECURSION 5)
--WHERE
--   [Level] = 2
ORDER BY
   [Level] ASC;