Mickey's T-SQL Ponderings

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

The Second Mission

This month our team is tackling windows functions. My particular take on the subject is near and dear to my reporting heart. How to get date ranges based on full months.

End of the Month

Dates tend to be a critical component of most metrics. They define the boundaries of the data. They create groups of data that can help with trending patterns and forecasting. The downside has always been the slicing and dicing of the date. For instance, if you need to group all the data by the last day of the month. You need to create an equation like this to determine the last day of the month:

DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + ‘-01-‘ + CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))

Or this:

DATEADD(month,1,DATEADD(DAY,-DATEPART(DAY,GETDATE()) – 1,GETDATE()))

In SQL2012 the EOMONTH() windows function was introduced. Now you just need to type this:

EOMONTH(SomeDate)

Performance Perks

Not only are you able to write a simpler equation that is easier to maintain and understand, but you get  some performance perks too.

I ran a query using both the “old way” and the “windows function way”  against a table I created with a million rows of data. The data set returns 561 rows of aggregated data. I aggregated all the data by using the last day of the month. I then looked at the execution plans and the statistics for these queries.

SELECT
     DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + '-01-' + 
          CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))
     ,COUNT(SomeDate)
FROM
     DemoProgramming.dbo.TestData AS td
GROUP BY
     DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + '-01-' + 
          CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))
ORDER BY
     DATEADD(day, -1, CONVERT(date, CONVERT(varchar(2), DATEPART(MONTH, DATEADD(month, 1, SomeDate))) + '-01-' + 
          CONVERT(varchar(4), DATEPART(year, DATEADD(month, 1, SomeDate)))))

SELECT
     EOMONTH(SomeDate)
     ,COUNT(SomeDate)
FROM
     DemoProgramming.dbo.TestData AS td
GROUP BY
     EOMONTH(SomeDate)
ORDER BY
     EOMONTH(SomeDate)

Findings

1. When breaking up a date to reconstruct it, you need to go back and forth between data types. The optimizer isn’t very fond of that. In fact you get a warning about the possibility of your cardinality being questionable. The EOMONTH() windows function doesn’t have this problem.

 

 

2. The EOMONTH() windows function takes less CPU and can return values faster. For this particular execution, I received a 5.25 times faster CPU time and 3.5 times faster execution time.

I used the following code to turn on Statistics:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

 

 

But Wait! There’s More

This particular function has an additional (optional) parameter. The parameter will allow you to add/subtract months from the end of the month. Which means you can easily find the beginning of the month using this equation:

SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) as boMonth

This also allows you to easily create a one year range for the last 12 full months using these two equations:
SELECT
DATEADD(DAY, 1, EOMONTH(GETDATE(), -13)) AS StartDate
,EOMONTH(GETDATE(),-1) AS EndDate

Outside the Box

You are probably telling yourself that you can do everything I just showed you by grouping data by the Month() function. Yes, that is true, but you need to think outside the box for the application. What about those wonderful companies who think Months are not contstrained by Day 1 and Day 31? (You know who you are. They have business rules like, “the 5th day of the month”. Ah! Now we got something we can work with.

SELECT DATEADD(DAY, 5, EOMONTH(GETDATE(), -1)) as FifthDayOfMonth

Don’t Stop Yet

If you want to read more about Windows Functions, don’t forget to check out these blog posts:

• Chris Yates: Windows Functions; Who Knew?
• Julie Koesmarno: ABC Classification With SQL Server Window Function
• Jeffrey Verheul: Write readable and high-performance queries with Window Functions

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

See you next month!!

Original Post: On a SQL Collaboration Quest