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
as
(select uplen = len(replace(floorstring,’)’,’’)
from Day1
)
, downcte
as
(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/
Pingback: Advent of Code–Day 2 – Voice of the DBA