CTE Examples (Adventureworks 2008)

Simple CTE Example using AdventureWorks2008

WITH StaffBasicsCTE (FirstName, LastName, JobTitle, MaritalStatus , Gender , Age) 
AS (
 SELECT   FirstName, LastName,JobTitle, MaritalStatus , Gender ,DATEDIFF(YEAR,[BirthDate],GETDATE()) AS Age
 FROM [AdventureWorks2008].[Person].[Person] p
 INNER JOIN [AdventureWorks2008].[HumanResources].[Employee] e
 ON p.BusinessEntityID = e.BusinessEntityID)
  
SELECT * FROM StaffBasicsCTE
WHERE Gender = 'F'
AND MaritalStatus = 'S'
AND Age < 35

Recursive CTE Example using AdventureWorks2008 (Adapted from stored procedure dbo.uspGetEmployeeManagers)

WITH [EmployeeManagersCTE] ([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) 
    AS (
        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 
        FROM [HumanResources].[Employee] e 
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]

        UNION ALL

        SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
        FROM [HumanResources].[Employee] e 
        INNER JOIN [EmployeeManagersCTE]
        ON e.[OrganizationNode].GetAncestor(1) = [EmployeeManagersCTE].[OrganizationNode]
        INNER JOIN [Person].[Person] p 
        ON p.[BusinessEntityID] = e.[BusinessEntityID]
        )

    SELECT 
          m.[BusinessEntityID]
        , m.[FirstName]
        , m.[LastName] -- Outer select from the CTE
        , m.[RecursionLevel]
        , m.[OrganizationNode].ToString() as [OrganizationNode]
        , p.[FirstName] AS 'ManagerFirstName'
        , p.[LastName] AS 'ManagerLastName' 
    FROM [EmployeeManagersCTE] m 
        INNER JOIN [HumanResources].[Employee] e 
        ON m.[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
            INNER JOIN [Person].[Person] p 
            ON p.[BusinessEntityID] = e.[BusinessEntityID]
    ORDER BY [RecursionLevel], m.[OrganizationNode].ToString()
    OPTION (MAXRECURSION 25)