Multiple CTEs – #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

One of the techniques that I find very handy in solving SQL problems is the CTE. This is much easier to read, for me, than embedding code into a derived table. For example, which of these is easier to decode, or perhaps more importantly, debug?

WITH calendarquarters (qtr, yr, dt)
AS
( SELECT qty = ‘Quarter ‘ + CAST(c.QtrNum AS VARCHAR(3))
       , yr = ‘Our year ‘ + CAST(YEAR(c.CalDate) AS VARCHAR(4))
       , c.CalDate
   FROM dbo.Calendar AS c
)
SELECT *
FROM calendarquarters cq
INNER JOIN dbo.CustomerSales AS s
ON cq.dt = s.LastSale
Or this:

SELECT *
FROM ( SELECT qty = ‘Quarter ‘ + CAST(c.QtrNum AS VARCHAR(3))
       , yr = ‘Our year ‘ + CAST(YEAR(c.CalDate) AS VARCHAR(4))
       , dt = c.CalDate
   FROM dbo.Calendar AS c
) cq
INNER JOIN dbo.CustomerSales AS s
ON cq.dt = s.LastSale

I’d argue the first becomes easier, especially when I have multiple tables in the join. In this way I can more easily see in the first example I’m joining two tables/views/CTEs together. If I want to know more about the details of one of those items, I can easily look up and see the CTE at the beginning.

However when I want multiple CTEs, how does this work?

That’s pretty easy. It’s actually just listing each CTE, separated by commas. In my case, I wanted to do this:

with upcte

(select uplen = len(replace(floorstring,’)’,’’)

from Day1

)

, downcte

(select downlen = len(replace(floorstring,’(’,’’)

from Day1

)

select uplen – downlen

from upcte, downcte

That’s it. I use the WITH once, and then each CTE stands along. I could add the column names if I wanted, but here I can easily see I’m querying two numbers from two “tables”, CTEs in this case, and performing subtraction. If I want the details, I get that from the previous definitions.

SQLNewBlogger

A quick post as I used this technique in solving Day 1 of the Advent of Code. This took about 5 minutes to write, and I got to add my own twist to the concept.

References

A quick one from my first Google result. While I knew how to do this, I double checked myself with a search.

Multiple CTE in One Select Statement Query – http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/