By: Paul Schuliger
Over a year ago I wrote an article that discussed using the SQL Window Function ROW_NUMBER() to set up a sort order within each layer of a hierarchy, and then showed how to use that information in Power BI.
I recently found myself on a reporting project once again using SQL Window Functions to provide the desired results. This time the project required the use of the SQL LEAD Window Function. This function allows a resultset to retrieve the value of the “next” row into its own result row – a very handy ability to have when trying to determine what to do with the “next” value. The implementation of the LEAD() function is straight forward, as you will see. The issue that I ran into was trying to determine the correct last calendar date for each month.
The Reporting Scenario
The report required a tracking of the status change of an item along with the date in which it changed. If the item changed status multiple times within a month, the desired results would only include the last status and date of the item in that month. And there was no guarantee that the last transaction was at the end of the month. The final goal was to show the life cycle of status changes by specific time periods and the corresponding financial amounts during those time periods.
In order to demonstrate the situation the example below uses the Microsoft AdventureWorksDW2012 database. The inner query against the DimDate table provides the “transactions” in this scenario. The data in the AdventureWorks “transactions” already have the correct end of the month dates, making this example easier to understand.
In case you are wondering, I needed to use SQL LEAD Window Functions because the “real world” results were not always divided nicely into consecutive months like the example is. So the LEAD function provides the results with the information to know the start date of the next time period in the results.
Once that information is returned, the DATEADD and the EOMONTH functions subtract one month from the “next” start date in order to get the end of the month that comes before the “next” start date.
For example, if the “next” start date is May 2005, the end of the month prior to that “next” start date is April 30th, 2005.
The outer query demonstrates the differences between DATEADD and EOMONTH in conjunction with the LEAD window function. Take a look at the results for the DateKey = 20050531 and the difference between the two functions.
• FN_DATEADD returns ‘2005-05-30’
• FN_EOMONTH returns ‘2005-05-31’
Initially, the report SQL was only using the DATEADD function to return the end of the month date. And that is where the issue with the incorrect end of month dates showed itself, and it was when I remembered that in SQL Server 2012, Microsoft provided the EOMONTH function for this specific situation. You provide a specific date and correct date data type of the EOMONTH function and it will provide the correct calendar date for the end of the month. After replacing the DATEADD function within the report SQL with the EOMONTH function, the results were exactly as desired.
Example #1: SQL of the LEAD function
Example #2: SQL of the LEAD function working with the DATEADD and the EOMONTH function
Example #3: DATEADD and EOMONTH function with transactions in the middle of the month
Questions?
Thanks for reading! We hope you found this blog post to be useful. Do let us know if you have any questions or topic ideas related to BI, analytics, the cloud, machine learning, SQL Server, (Star Wars), or anything else of the like that you’d like us to write about. Simply leave us a comment below, and we’ll see what we can do!
Keep your data analytics sharp by subscribing to our mailing list
Get fresh Key2 content around Business Intelligence, Data Warehousing, Analytics, and more delivered right to your inbox!
Key2 Consulting is a data warehousing and business intelligence company located in Atlanta, Georgia. We create and deliver custom data warehouse solutions, business intelligence solutions, and custom applications.