This was the technique I ended up using in my query which inspired this article. Again, I had help from Copilot to author the following article about using the CTE (Common Table Expression) syntax:
When writing SQL queries, especially those involving multiple layers of calculations or transformations, your code can quickly become difficult to read and maintain. This is where Common Table Expressions (CTEs) come in. CTEs allow you to break down complex logic into manageable, readable parts—almost like building blocks.
In this article, we’ll explore how to use CTEs in SQL Server, starting with a simple example and then moving into a more advanced use case involving multiple levels of calculated columns.
🧱 What is a CTE?
A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword and can be thought of as a named subquery.
Basic Syntax:
WITH CTE_Name AS (
SELECT ...
)
SELECT * FROM CTE_Name;
✅ Simple Example: Filtering and Sorting
Let’s say you have a Products table and you want to first filter products by category and then sort them by price.
WITH FilteredProducts AS (
SELECT ProductID, ProductName, Category, Price
FROM Products
WHERE Category = 'Electronics'
)
SELECT *
FROM FilteredProducts
ORDER BY Price DESC;
This makes your query easier to read and maintain, especially if the filtering logic becomes more complex.
🔄 Advanced Example: Multi-Level Calculated Columns
Now let’s look at a more advanced example where you need to perform multiple levels of calculations. Suppose you have a Sales table and you want to:
- Calculate the subtotal (quantity × unit price)
- Calculate the tax (10% of subtotal)
- Calculate the total (subtotal + tax)
Instead of repeating formulas, you can use nested CTEs to build each step on top of the previous one.
Sample Table:
CREATE TABLE Sales (
SaleID INT,
Quantity INT,
UnitPrice DECIMAL(10, 2)
);
Query with Nested CTEs:
-- Step-by-step calculation using CTEs
WITH Subtotals AS (
SELECT
SaleID,
Quantity,
UnitPrice,
Quantity * UnitPrice AS Subtotal
FROM Sales
),
Taxes AS (
SELECT
SaleID,
Subtotal,
Subtotal * 0.10 AS Tax
FROM Subtotals
),
FinalTotals AS (
SELECT
s.SaleID,
s.Quantity,
s.UnitPrice,
t.Subtotal,
t.Tax,
t.Subtotal + t.Tax AS Total
FROM Sales s
JOIN Taxes t ON s.SaleID = t.SaleID
)
SELECT * FROM FinalTotals;
🧠 Why Use CTEs for Calculations?
- Clarity: Each step is clearly defined and easy to follow.
- Reusability: You avoid repeating complex expressions.
- Maintainability: Changes to logic only need to be made in one place.
🏁 Final Thoughts
CTEs are a powerful tool in SQL Server that help you write cleaner, more maintainable queries. Whether you’re simplifying a filter or building multi-step calculations, CTEs let you structure your logic in a way that’s easy to understand and debug.