Thursday, September 04, 2008

Group By Time: The Microsoft SQL Server 2005 Way

Statistics, we all seem to need them, but it is not always easy getting them exactly the way you would like. This article is going to explore the simple task of grouping data by hour spiced up a bit by displaying results in standard time format with AM/PM instead of military time and groupings every 30 minutes.
Say you have the following data.

Table: CustomerCalls


Desired Report


Starting with the basics, we do the following:


SELECT DateName(hh, CallDateTime) As [Hour], Count(*) As [Calls]

FROM CustomerCalls

GROUP BY DateName(hh, CallDateTime)

Using the DateName function, we grab the hour portion of datetime column in our table and group rows and count. All is well and we get results like this by military time hour of the day:



Great! But now we need to put in a little bit of fancy formatting to change 0100 to 01:00AM for the non-combat oriented among us.


SELECT Right('0' + Case When DateName(hh, CallDateTime) = 0 Then '12'
When DateName(hh, CallDateTime) <= 12 Then DateName(hh, CallDateTime)
Else DateName(hh, DateAdd(hh, -12, CallDateTime)) End, 2) + ':' +
Case When DateName(n, CallDateTime) >= 30 Then '30' Else '00' End +
Case When DateName(hh, CallDateTime) < 12 Then 'AM' Else 'PM' End As [Hour],
Count(*) As [Calls]
FROM CustomerCalls(NoLock)
GROUP BY Right('0' + Case When DateName(hh, CallDateTime) = 0 Then '12'
When DateName(hh, CallDateTime) <= 12 Then DateName(hh, CallDateTime)
Else DateName(hh, DateAdd(hh, -12, CallDateTime)) End, 2) + ':' +
Case When DateName(n, CallDateTime) >= 30 Then '30' Else '00' End +
Case When DateName(hh, CallDateTime) < 12 Then 'AM' Else 'PM' End


Life is all well and good again. Well maybe. That code is crazy as we must have the entire case logic in the group by statement to not violate SQL syntax rules when grouping, so what to do next. Well good news for us there is the WITH statement in SQL Server 2005 for creating Common Table Expressions (CTE). So diving into CTE, the code all together including some added functionality to sort in correct time of day order is below:



WITH TableByTime As (
SELECT Cast(DateName(hh, CallDateTime) As Int) As [Sort],
Right('0' + Case When DateName(hh, CallDateTime) = 0 Then '12'
When DateName(hh, CallDateTime) <= 12 Then DateName(hh, CallDateTime)
Else DateName(hh, DateAdd(hh, -12, CallDateTime)) End, 2) + ':' +
Case When DateName(n, CallDateTime) >= 30 Then '30' Else '00' End +
Case When DateName(hh, CallDateTime) < 12 Then 'AM' Else 'PM' End As [Hour],
Customer
FROM CustomerCalls (NoLock)
)
SELECT Hour, Count(*)
FROM TableByTime
GROUP BY Sort, Hour
ORDER BY Sort, Hour


Wonderful! And code is not too unmanageable in my humble opinion. Probably can break it down some more, but as you see the benefits of CTE has already improved readability that I stopped here for implementation.

Let’s take a deeper look at the code since we glossed over this earlier. First column takes the normal military hour ensuring it is functioning as an integer for use as our sorting mechanism later since time in military notation goes from 0 – 23 which is nicely ordered for us already. Second column, [Hour], is our case when breaking down datetime column to standard time of day: 12:00AM – 11:30PM as we also group the times within the first and last 30 minutes of each hour. The third column (could be columns) is data we want from the table – used Customer which we didn’t really need for our aggregate which is only a count; however, if we were doing sums or other statistical analysis on numerical data in the CustomerCalls table we could add those columns here.

Now that we have our CTE, you can see the subsequent T-SQL statement where we actually implement the GROUP BY statement is drastically simplified as we have [Hour] column to call directly instead of the entire case logic. We ORDER BY our sort column and then by our hour and presto we have our results.

In conclusion, using Common Table Expressions in SQL Server 2005 adds a great deal of power and flexibility beyond my simple case here while streamlining SQL statements to be easier to maintain/read, so read up and enjoy the cool new features of Microsoft SQL Server.


References:

No comments: