-- -------------------------------------------------------------------------------- -- post0019 -- sample 1 -- statement 2 -- -------------------------------------------------------------------------------- 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] -- ,[HireDate] -- ,[BirthDate] -- ,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.[HireDate] -- ,T01.[BirthDate] -- ,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' -- ) -- 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 ,[HireDate] -- SQL Server will throw an exception ,[BirthDate] -- # The option cannot be used within a CTE -- # More information on recursive CTEs you can find in the Online Documentation -- ,NTILE(3) -- >> Classifies the hire dates by Level (3 levels) OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [HireDate], [EmployeeKey] -- - ORDER BY clause ) AS [HireDate_NTILE] -- [EmployeeKey] ensures an reprducable sort order ,DENSE_RANK() -- >> Orders the hire dates by Level OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [HireDate] -- - ORDER BY clause ) AS [HireDate_DENSE_RANK] -- ,NTILE(3) -- >> Classifies the birth dates by Level (3 levels) OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [BirthDate], [EmployeeKey] -- - ORDER BY clause ) AS [BirthDate_NTILE] -- [EmployeeKey] ensures an reprducable sort order ,DENSE_RANK() -- >> Orders the birth dates by Level OVER (PARTITION BY [Level] -- - PARTITOIN clause ORDER BY [BirthDate] -- - ORDER BY clause ) AS [BirthDate_DENSE_RANK] -- FROM -- CTE_Employee -- -- -------------------------------------------------------------------------------- -- Check, whether the result of the SSIS solution is identical to the result of the -- above statement by uncommenting the following statement. The result of the -- complete statement maust be an empty set of rows. -- -------------------------------------------------------------------------------- --EXCEPT --SELECT -- [ParentEmployeeKey] -- ,[EmployeeKey] -- ,[LastName] -- ,[FirstName] -- ,[Title] -- ,[Level] -- ,[HireDate] -- ,[BirthDate] -- ,[HireDate_NTILE] -- ,[HireDate_DENSE_RANK] -- ,[BirthDate_NTILE] -- ,[BirthDate_DENSE_RANK] --FROM -- [AdventureWorksDW2017].[dbo].[post00190004];