To CTE or not to CTE

Requirement

Not long ago, I was working with a client to put together a SQL query to extract sales figures, by month, for the next 5 years.

I began working on this right away, typing as quickly as the ideas came into my head.

Problem

Before I knew it, the query was long! It had repeated logic and included sub queries within sub queries- safe to say it was a mess! For readability and ongoing maintenance, I needed a way to break this query down into more manageable pieces.

Solution

A view was not the right option because it wouldonly be used once. Even though it would simplify the query slightly, it didn'tresolve all of my problems.

A derived table would not work as I needed to use recursion , therefore I found, the best and only option to meet all of my requirements was a CTE.

In SQL, a CTE (Common Table Expression) is used tosimplify complex joins and subqueries. A CTE acts much the same way as atemporary/derived table by defining a temporary result set that can be usedwithin another query, in addition it can also reference itself- something a subquery cannot do.

The main hurdle the CTE helped me overcome was anissue whereby the months were omitted from my result set if no sales figureswere available. The requirement was clear- I had to return sales figures forall months, which meant I needed to include zeros if there were no sales.

I used a recursive CTE to generate the months inthe year, so I could left join with the sales figures to give me a record foreach month, regardless of sales figure. See an example below:

;WITH months(MonthNumber) AS
(
SELECT 1
UNION ALL
SELECT MonthNumber+1
FROM months
WHERE MonthNumber < 12
)

EUREKA!

The data was correct! Using a CTE was the right path to go down on this occasion, and I will definitely be considering them more going forward!