CREATE PROCEDURE [dbo].[spInsertPost0021] AS BEGIN TRUNCATE TABLE [dbo].[post00210001]; WITH CTE_Employee AS -- -------------------------------------------------------------------------------- ( -- SELECT -- This SELECT statement returns the anchor element of the recursive query. The [EmployeeKey] -- anchor element is the top level Employee of Adventure Works, the CEO. ,[FirstName] -- ,[LastName] -- ,[Title] -- ,[ParentEmployeeKey] -- ,[VacationHours] -- ,[SickLeaveHours] -- ,1 AS [Level] -- >> The field Level is used to calculate the hierarchy level of an employee. FROM -- The CEO is on Level 1. All top level managers are on Level 2. [dbo].[DimEmployee] -- Regional Managers, Technical supervisors etc. are on Level 3 WHERE -- [ParentEmployeeKey] IS NULL -- AND [Status] = 'Current' -- UNION ALL -- >> The operator UNION ALL is the only allowed operator allowed between the SELECT -- anchor and the the first recursive member. T01.[EmployeeKey] -- ,T01.[FirstName] -- ,T01.[LastName] -- ,T01.[Title] -- ,T01.[ParentEmployeeKey] -- ,T01.[VacationHours] -- ,T01.[SickLeaveHours] -- ,T02.[Level] + 1 AS [Level] -- >> Increases the level for each recursion FROM -- [dbo].[DimEmployee] T01 -- INNER JOIN CTE_Employee T02 -- ON -- T01.[ParentEmployeeKey] = T02.[EmployeeKey] -- WHERE -- T01.[Status] = 'Current' -- ) -- INSERT INTO [dbo].[post00210001] -- ( -- [ParentEmployeeKey] -- ,[EmployeeKey] -- ,[LastName] -- ,[FirstName] -- ,[Title] -- ,[Level] -- ,[VacationHours] -- ,[SickLeaveHours] -- ,[VacationHours_NTILE] -- ,[VacationHours_DENSE_RANK] -- ,[SickLeaveHours_NTILE] -- ,[SickLeaveHours_DENSE_RANK] -- ) -- SELECT -- # If the recursive member query definition returns the same values for both [ParentEmployeeKey] -- # The number of recursions can be limited with the option MACRECURSION ,[EmployeeKey] -- the parent and child columns, an infinite loop is created. To avoid an ,[LastName] -- infinite loop you can limit the number of recursions. By default SQL ,[FirstName] -- Server limits the recursions to 100. The maximium numbver is limited to ,[Title] -- 32767 recursions. ,[Level] -- # If the number of recursion exceeds the specified value for MACRECURSION ,[VacationHours] -- SQL Server will throw an exception ,[SickLeaveHours] -- # The option cannot be used within a CTE -- # More information on recursive CTEs you can find in the Online Documentation -- ,NTILE(3) -- >> Classifies the vacation hours by Level (3 levels) OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [VacationHours], [EmployeeKey] -- - ORDER BY clause ) AS [VacationHours_NTILE] -- [EmployeeKey] ensures an reproducable sort order ,DENSE_RANK() -- >> Orders the vacation hours by Level OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [VacationHours] -- - ORDER BY clause ) AS [VacationHours_DENSE_RANK] -- ,NTILE(3) -- >> Classifies the sick hours by Level (3 levels) OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [SickLeaveHours], [EmployeeKey] -- - ORDER BY clause ) AS [SickLeaveHours_NTILE] -- [EmployeeKey] ensures an reproducable sort order ,DENSE_RANK() -- >> Orders the sick hours by Level OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [SickLeaveHours] -- - ORDER BY clause ) AS [SickLeaveHours_DENSE_RANK] -- FROM -- CTE_Employee; -- END -- [dbo].[spInsertPost0021]