Refactoring T-SQL with Windowing Functions

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

Script 2: The Old Way of Querying

Script 3: The New Way of Querying

Previous Post
How to Add Nonclustered Indexes to Clustered Columnstore Indexes
Next Post
Microsoft Cloud Rebranded as Microsoft Pale Blue

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

    Reply
    • 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.

      Reply
      • 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 🙂

        Reply
        • 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.

          Reply
  • 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.

    Reply
    • 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.).

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}