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 |
Bob Pusateri
A very nifty demonstration! Thanks so much for sharing this as part of T-SQL Tuesday!
T-SQL Tuesday #18 Wrapup | Bob Pusateri - The Outer Join
[…] L. Davis (blog | @SQLSoldier) – Robert shows us a nifty setup using CTEs to create date ranges in a pivot table and return everything nice and pretty in one fell […]