WITH (common table expressions)

Common table expressions are a good way to reuse a piece of SQL.

;WITH Sales_CTE
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

Bonus points: recursive CTEs

The general form is:

Define a base case.
UNION ALL
Define a recursive case.

Example:

;WITH cte AS
(
	-- Define a base case
	SELECT CAST('/' + Name AS VARCHAR(50)) as 'CteName', ID
	FROM dbo.YourTable
	WHERE parent_id IS NULL

	-- UNION ALL
	UNION ALL

	-- Define a recursive case
	SELECT CAST(cte.CteName + '/' + Name AS VARCHAR(50)), t.ID
	FROM dbo.YourTable t
	INNER JOIN cte ON t.parent_id = cte.id
)
SELECT cteName FROM cte
ORDER BY ID

Multiple CTEs

To define multiple....

;WITH Sales_CTE
AS
-- Define the First CTE query.
(
	SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
	FROM Sales.SalesOrderHeader
	WHERE SalesPersonID IS NOT NULL
),
-- ^ Comma, followed by the Next CTE
Miles_Driven_CTE AS
(
	Select SalesPersonID, YEAR(TripDate) as Year, SUM(Distance) as Miles
	FROM Vehicle.Usage
	GROUP BY SalesPersonID, YEAR(TripDate)
)
-- Define the outer query referencing ANY/ALL OF the CTE name.
SELECT
	s.SalesPersonID,
	COUNT(s.SalesOrderID) AS TotalSales,
	m.Miles,
	m.Year
FROM
	Sales_CTE s
INNER JOIN
	Miles_Driven_CTE m on s.SalesPersonID = m.SalesPersonID
		AND m.Year = s.SalesYear
GROUP BY
	m.Year,
	s.SalesPersonID
ORDER BY
	s.SalesPersonID,
	m.Year desc;
GO

Note that a CTE can refer to any of the preceeding CTEs.

And that we often write a ';' before the 'WITH' so that the compiler is happy. Compilers have inflexible wishes.