You’ve been querying comparative numbers like Year To Date and Same Period Last Year by using tedious CTEs and subqueries. Beginning with SQL Server 2012, getting these numbers is easier than ever! Join Doug for a 30-minute T-SQL tune-up using window functions that will cut down dramatically on the amount of code you need to write.
Looking for the scripts? Grab them below the video!
Script 1: Create Windowing View
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
--****************** -- (C) 2014, Brent Ozar Unlimited (TM) -- See https://www.brentozar.com/go/eula for the End User Licensing Agreement. --WARNING: --This script suitable only for test purposes. --Do not run on production servers. --****************** /* Script 1: Create Windowing View Note: This script requires the AdventureWorks2012 database, which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330 */ USE AdventureWorks2012 GO /* Duplicates the data with a DueDate field set to [year] - 1. */ CREATE VIEW vWindowing AS ( SELECT CustomerID , CAST(SUM(TotalDue) AS DECIMAL(18,2)) AS TotalDue , DATEPART(mm, DueDate) AS DueMonth , CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1 WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2 WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3 WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4 ELSE -1 END AS DueQtr , DATEPART(yy, DueDate) AS DueYear FROM sales.SalesOrderHeader GROUP BY CustomerID , DATEPART(mm, DueDate) , CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1 WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2 WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3 WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4 ELSE -1 END , DATEPART(yy, DueDate) UNION SELECT CustomerID , CAST(SUM(TotalDue) AS DECIMAL(18,2)) AS TotalDue , DATEPART(mm, DueDate) AS DueMonth , CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1 WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2 WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3 WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4 ELSE -1 END AS DueQtr , DATEPART(yy, DueDate) - 1 AS DueYear FROM sales.SalesOrderHeader GROUP BY CustomerID , DATEPART(mm, DueDate) , CASE WHEN DATEPART(mm, DueDate) BETWEEN 1 AND 3 THEN 1 WHEN DATEPART(mm, DueDate) BETWEEN 4 AND 6 THEN 2 WHEN DATEPART(mm, DueDate) BETWEEN 7 AND 9 THEN 3 WHEN DATEPART(mm, DueDate) BETWEEN 10 AND 12 THEN 4 ELSE -1 END , DATEPART(yy, DueDate) - 1 ) GO |
Script 2: The Old Way of Querying
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 |
--****************** -- (C) 2014, Brent Ozar Unlimited (TM) -- See https://www.brentozar.com/go/eula for the End User Licensing Agreement. --WARNING: --This script suitable only for test purposes. --Do not run on production servers. --****************** /* Script 2: The Old Way of Querying Note: This script requires the AdventureWorks2012 database, which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330 */ USE AdventureWorks2012 go SET STATISTICS IO ON GO /* --------------------------------------- */ -- The old way to blend YTD and detail. -- Pretty simple so far with just one join. /* --------------------------------------- */ SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue , SUM(vsum.TotalDue) AS YTDTotalDue FROM dbo.vWindowing AS vdet JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth WHERE vdet.CustomerID = 11091 GROUP BY vdet.CustomerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue ORDER BY vdet.DueYear, vdet.DueQtr, vdet.DueMonth /* --------------------------------------- */ -- That's great, but what if you need other metrics? -- How do we put QTD and YTD in the same T-SQL statement? /* --------------------------------------- */ SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue , SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue , SUM(vsum.TotalDue) AS YTDTotalDue FROM dbo.vWindowing AS vdet LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth WHERE vdet.CustomerID = 11091 GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue ; /* --------------------------------------- */ -- Looks good. What about same YTD last year? /* --------------------------------------- */ SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue , SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue , SUM(vsum.TotalDue) AS YTDTotalDue , SUM(vsum2.TotalDue) AS LastYTDTotalDue FROM dbo.vWindowing AS vdet LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth LEFT JOIN dbo.vWindowing AS vsum2 ON vsum2.CustomerID = vdet.CustomerID AND vsum2.DueYear = vdet.DueYear - 1 AND vsum2.DueMonth <= vdet.DueMonth WHERE vdet.CustomerID = 11091 GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue ; /* --------------------------------------- */ -- And now we have problems. What WILL give us -- the Last YTD number we want? /* --------------------------------------- */ ; WITH va AS (SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue , SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue , SUM(vsum.TotalDue) AS YTDTotalDue FROM dbo.vWindowing AS vdet LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth WHERE vdet.CustomerID = 11091 GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue ) SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue , vdet.QTDTotalDue , vdet.YTDTotalDue , vsum.QTDTotalDue AS SameQTDLastYearTotalDue , vsum.YTDTotalDue AS SameYTDLastYearTotalDue FROM va AS vdet LEFT JOIN va as vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueMonth = vdet.DueMonth AND vsum.DueYear = vdet.DueYear - 1 /* --------------------------------------- */ -- That's great but now we're four joins deep. -- How much deeper dare we go? Roll 2d8 to find out. -- Roll Joins -- ----- ----- -- 2-6 6 -- 7-10 8 -- 11-15 10 -- 16 Save vs. Traps or suffer 1d6 + 2 damage. -- And how's the execution plan looking? /* --------------------------------------- */ /* --------------------------------------- */ -- Let's add the TotalDue for everyone over that same period -- ...if we dare. /* --------------------------------------- */ ; WITH va AS (SELECT vdet.customerID, vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue , SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue , SUM(vsum.TotalDue) AS YTDTotalDue FROM dbo.vWindowing AS vdet LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth WHERE vdet.CustomerID = 11091 GROUP BY vdet.CustomerID, vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue ) , vAll as (SELECT vdet.DueMonth, vdet.DueQtr, vdet.DueYear, vdet.TotalDue , SUM(CASE WHEN vsum.DueQtr = vdet.DueQtr AND vsum.DueMonth <= vdet.DueMonth THEN vsum.TotalDue ELSE 0 END) AS QTDTotalDue , SUM(vsum.TotalDue) AS YTDTotalDue FROM dbo.vWindowing AS vdet LEFT JOIN dbo.vWindowing AS vsum ON vsum.CustomerID = vdet.CustomerID AND vsum.DueYear = vdet.DueYear AND vsum.DueMonth <= vdet.DueMonth GROUP BY vdet.DueYear, vdet.DueQtr, vdet.DueMonth, vdet.TotalDue ) SELECT * FROM vAll -- ...and so on... /* --------------------------------------- */ -- I'll just stop here and say -- it's a whole lotta work. /* --------------------------------------- */ |
Script 3: The New Way of Querying
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
--****************** -- (C) 2014, Brent Ozar Unlimited (TM) -- See https://www.brentozar.com/go/eula for the End User Licensing Agreement. --WARNING: --This script suitable only for test purposes. --Do not run on production servers. --****************** /* Script 3: The New Way of Querying Note: This script requires the AdventureWorks2012 database, which can be found here: https://msftdbprodsamples.codeplex.com/releases/view/55330 */ USE AdventureWorks2012 GO SET STATISTICS IO ON GO /* --------------------------------------- */ -- Remember the old way got us roughly one -- table join per metric? -- -- Hold on to your seat. /* --------------------------------------- */ SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue FROM dbo.vWindowing WHERE CustomerID = 11091 ORDER BY DueYear, DueMonth /* We'll start slow by just adding QTD */ SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue FROM dbo.vWindowing WHERE CustomerID = 11091 ORDER BY DueYear, DueMonth /* + YTD */ SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDTotalDue FROM dbo.vWindowing WHERE CustomerID = 11091 ORDER BY DueYear, DueMonth /* What are we waiting for -- let's really see what this can do! */ SELECT customerID, DueMonth, DueQtr, DueYear, TotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS QTDTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID) AS AllTimeTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotalTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS RemainingFutureTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) /SUM(TotalDue) OVER (PARTITION BY CustomerID) AS PctRemainingFutureTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr ORDER BY DueMonth) AS ThisQtrTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth) AS ThisYearTotalDue , SUM(TotalDue) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS Rolling12TotalDue , TotalDue/SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear, DueQtr) AS PctOfThisQtrTotalDue , TotalDue/SUM(TotalDue) OVER (PARTITION BY CustomerID, DueYear) AS PctOfThisYearTotalDue , LAG(TotalDue, 1) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS PrevMonthTotalDue , LAG(TotalDue, 3) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS SameMonthPrevQtrTotalDue , LAG(TotalDue, 12) OVER (PARTITION BY CustomerID ORDER BY DueYear, DueMonth) AS SameMonthPrevYearTotalDue , AVG(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDAvgTotalDue /* ------------------ */ -- If I want to see how this customer stacks up to others, -- I can remove the where clause, add these columns, and filter at the client --, AVG(TotalDue) OVER (PARTITION BY DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDAvgTotalDueAllCustomers --, AVG(TotalDue) OVER (PARTITION BY CustomerID, DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- / AVG(TotalDue) OVER (PARTITION BY DueYear ORDER BY DueMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PctVarYTDAvgTotalDueVsAllCustomers /* ------------------ */ FROM dbo.vWindowing AS va WHERE CustomerID = 11091 ORDER BY CustomerID, DueYear, DueMonth |
6 Comments. Leave new
Good post, I have one thing to ask.
Why are you doing DATEPART(mm, DueDate) with a CASE statement instead of DATEPART(qq, DueDate) ? qq will give you the quarter
SELECT DATEPART(qq,’20140331′),
DATEPART(qq,’20140630′),
DATEPART(qq,’20140930′),
DATEPART(qq,’20141231′)
1 2 3 4
Denis
Denis – I can’t speak for Doug (he’s out house shopping today) but I usually do it with a case statement because often financial quarters are different than calendar quarters.
Makes sense in that case, a calendar table with a fiscal quarter column would probably make sense as well. Of course I understand the objective of the post is to show the windowing functions and not how to create an accounting system 🙂
You’re both absolutely right. At my last job, everything revolved around the fiscal year (July-June) so that’s how I would have hacked it in the absence of a DimDate table. I didn’t want to complicate the example by introducing DimDate, so I CASEd it out in the code instead. If I didn’t care about anything other than calendar quarters, the DATEPART code would be simpler, definitely.
Great post. Just a comment and that is with the windowing functions, one is able to replicate some of the MDX statements used in SSAS. And that is great for SQL developers who do not necessarily want to build cubes.
Thanks! That was my reaction too when I first saw what windowing functions could do: MDX-like results in T-SQL (previous/next members, scoping, etc.).