text: USE AdventureWorks2008R2;
  GO
  -- Define the CTE expression name and column list.
  WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
  AS
  -- Define the CTE query.
  (
      SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
      FROM Sales.SalesOrderHeader
      WHERE SalesPersonID IS NOT NULL
  )
  -- Define the outer query referencing the CTE name.
  SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
  FROM Sales_CTE
  GROUP BY SalesYear, SalesPersonID
  ORDER BY SalesPersonID, SalesYear;
  GO
  
  