A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
-- Define the CTE query.
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
WHERE SalesPersonID IS NOT NULL
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;