T-SQL Tuesday #18 – Using CTEs to Pivot Data Into Date Ranges

T-SQL Tuesday #18 – Using CTEs to Pivot Data Into Date Ranges

This blog entry is participating in T-SQL Tuesday #18, hosted this month by Bob Pusateri (Blog|@SQLBob). You are invited to visit his blog to join the blog party. You are welcome to write your own participating blog post for the party or just to read more blogs participating in this month’s theme: Common Table Expressions or CTEs (not to be confused with coefficient of thermal expansion, but if you made that mistake, don’t worry, most people do).

Useful for Date Ranges in Pivots

I found that I use CTEs a LOT!! They are very handy for when I need to define something that isn’t defined to use later in a query. In today’s example, I’m using a CTE to define a set of defined ranges to place data into. Then I will pivot the data and make the date ranges my column headers. I have found many applications for this process.

Before we dive in, let me just say that there are many different ways to go about doing this without CTEs or Pivots. This is just my preferred way to do it. Many alternate ways may include performing multiple self-joins to get the data into different columns, and in those cases, Pivot is a much more performant methodolgy to use. CTEs simply make it easy to do and easy for someone else to understand.

Now, I’m going to create a table in a test database on my laptop, populate with some data from teh AdventureWorks2008R2 database, and then mock up the dates a little to give us some good data to work with.

-- Drop table if exists
If OBJECT_ID('dbo.CTETest') Is Not Null
    Drop Table dbo.CTETest;
Go

-- Create table from WorkOrder table in AdventureWorks2008R2
Select * Into dbo.CTETest
From AdventureWorks2008R2.Production.WorkOrder
Go

-- Change the EndDate of select rows to give good assortment of values
Declare @cntr int = 2

While @cntr <= 7
  Begin
    Update dbo.CTETest
    Set EndDate = DateAdd(day, @cntr, StartDate)
    Where WorkOrderID % @cntr = 0
    
    Set @cntr = @cntr + 1
  End
Go

Now that we have the table and data set up, I’m going to query the data to get the difference between the start and end dates, sort them into their respective date ranges that I’ll define in the first CTE, and then Pivot the date range data into columns.

-- Query the data for date spans and pivot into specific ranges
With DayRanges (RangeName, RangeLow, RangeHigh)
As (Select '< 1 Day', 0, 0
    Union Select '1 - 2 Days', 1, 2
    Union Select '3 - 5 Days', 3, 5
    Union Select '6 - 10 Days', 6, 10
    Union Select '> 10 Days', 11, 999),
Orders (ProductID, ShipDelay)
As (Select ProductID, DateDiff(day, StartDate, EndDate)
    From dbo.CTETest)
Select ProductID,
    ISNULL([< 1 Day], 0) As '< 1 Day',
    ISNULL([1 - 2 Days], 0) As '1 - 2 Days',
    ISNULL([3 - 5 Days], 0) As '3 - 5 Days',
    ISNULL([6 - 10 Days], 0) As '6 - 10 Days',
    ISNULL([> 10 Days], 0) As '> 10 Days'
From (Select O.ProductID, DR.RangeName, COUNT(*) As theCount
    From Orders O
    Inner Join DayRanges DR On O.ShipDelay Between DR.RangeLow And DR.RangeHigh
    Group By O.ProductID, DR.RangeName) As Pvt
Pivot (Min(theCount)
    For RangeName In ([< 1 Day],
        [1 - 2 Days],
        [3 - 5 Days],
        [6 - 10 Days],
        [> 10 Days])) As Pvt2
Order By ProductID

Here’s partial output of what the query returns:

ProductID	< 1 Day	1 – 2 Days	3 – 5 Days	6 – 10 Days	> 10 Days
331	0	129	399	565	0
400	0	102	312	410	0
401	0	132	384	576	0
514	0	38	126	90	72
515	0	1	2	5	4