SQL Saturday #492 Follow Up – A Window into Your Data

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions and Comments

1. Does RATIO_TO_REPORT exist in SQL Server? It is in Oracle.
   • Currently this function is not available in SQL Server
   • Here is the equivalent functionality using existing functions in SQL Server:

     • OrderAmt / SUM(OrderAmt) OVER (PARTITION BY OrderDate)

     • This example can use the source code I have referenced below. It uses the current value as the numerator and the sum by partition as the denominator. While not a simple function, the equivalent is still fairly simple using window functions to help.
2. Demo issues with Azure SQL Database
   • During the session I ran into an issue with Azure SQL Database. It turns out that the following two functions are not supported there.
     • PERCENTILE_CONT
     • PERCENTILE_DISC

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

• OVER Clause: http://msdn.microsoft.com/en-us/library/ms189461(v=SQL.110).aspx
• Analytic Functions: http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx