As far as I know you can't use UNION on two separate CTE's.
Some options you have:
- Place queries inside a single CTE and use union inside the one CTE
- Don't use CTE but 'traditional' SELECT statements
- Create a view for a single CTE and combine them
Examples
All-in
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
UNION ALL
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
No CTE
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
UNION ALL
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
View
CREATE VIEW Sales1 AS
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
CREATE VIEW Sales2 AS
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
SELECT SalesPersonID,TotalSales, SalesYear FROM Sales1
UNION
SELECT SalesPersonID,TotalSales, SalesYear FROM Sales2