Fun With Common Table Expressions

I came across some interesting ways to use common table expressions (CTE) in SQL Server and pass them on in the hope someone may find them useful. The first example shows how to use a CTE to update a table, and the second illustrates a technique for comparing a row with a previous row.

Update a CTE

Suppose you inherit a table that keeps track of tools. For this example, assume tools reside in toolboxes, only in boxes, and only one box at a time. Furthermore, the toolboxes cannot be nested like matryoshka. The rows are in no particular order:

Toolbox | Tool 
----------------- 
Red     | Pliers 
Red     | Hammer 
Red     | Robertson screwdriver 
Blue    | Wire cutters 
Blue    | Utility knife 
Blue    | Phillips screwdriver

Now suppose your requirements change and you must assign a number to each tool, by toolbox. You could use a cursor to iterate over the rows and maintain a counter, but I’ve always found them clumsy.

Add a new integer column, RECORD_NUM and run a CTE to generate a count for each row by toolbox, and then update the CTE with the count:

;WITH CTE_RN AS (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY TOOLBOX ORDER BY (SELECT 1)) AS RN,
    RECORD_NUM FROM TOOLBOX ) 
UPDATE CTE_RN SET CTE_RN.RECORD_NUM=CTE_RN.RN

Nice and neat. RECORD_NUM must be selected by the CTE in order to make it available to the update statement. The SELECT 1 in the ORDER BY clause does not provide order of any kind, which is fine because the rows are in no particular order. When run, is updates the table to the following:

Toolbox | Tool                  | RECORD_NUM
--------------------------------------------
Red     | Pliers                | 1
Red     | Hammer                | 2
Red     | Robertson screwdriver | 3 
Blue    | Wire cutters          | 1
Blue    | Utility knife         | 2
Blue    | Phillips screwdriver  | 3

Compare with Previous Row

Computing the difference of a value between one date and the next is a common task, but you have to jump through a few hoops to get an answer with SQL.

Here we have FOO.FOOBAR and we want to know the change of VALUE from month to month.

DATE       | VALUE
------------------
2018-01-01 | 10
2018-02-01 | 20
2018-03-01 | 30
2018-04-01 | 40
2018-05-01 | 50

CTEs and ranking functions provide a clean implementation.

;WITH CTE_DATES AS (
  SELECT [DATE], VALUE,
  DENSE_RANK OVER (ORDER BY [DATE] DESC) AS RK
  FROM FOO.FOOBAR )
SELECT
  CUR.DATE AS CURRENT,
  PREV.DATE AS PREVIOUS,
  CUR.VALUE - PREV.VALUE AS DIFF FROM CTE_DATES CUR
  INNER JOIN CTE_DATES PREV ON PREV.RK=(CUR.RK + 1)
  ORDER BY CUR.DATE DESC

DENSE_RANK() produces a continuous series, with no gaps. This continuity guarantees that the inner join on line 10 matches with the previous row. It generates the following results:

CURRENT    | PREVIOUS   | DIFF
------------------------------
2018-05-01 | 2018-04-01 | 10 
2018-04-01 | 2018-03-01 | 10 
2018-03-01 | 2018-02-01 | 10 
2018-02-01 | 2018-01-01 | 10 

There a a number of other ways to do this kind kind of row-by-row comparison, but this method is the cleanest I have come across.