Last time we looked at grouping by time for our customer calls statistics by the half an hour. We ended with this code based on SQL 2005 common table expression syntax.
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
We can be satisfied with this, but why waste a perfectly good opportunity to keep learning. In all seriousness, breakdowns by date/time are statistics I often have to get, so if you are anything like me it would be good to explore making this more reusable and streamlined. Since what we have above is very clean compared to the starting point, what we have left to do is create a function to emulate the TimeSerial function from Microsoft Access that does what our case statements are doing and more.
In summary, TimeSerial, takes in hour in military notation (i.e. 14 for 2PM), minutes, and seconds and translates to appropriate time in format hh:mm:ss with AM/PM indicator. In addition to straight time display, it could do calculations for you based on varying inputs and use of negatives. If you want more information on how TimeSerial functions, see reference for function in Microsoft Access below.
So diving in, we can write a function like this that adds TimeSerial to Microsoft SQL Server using a user defined function.
CREATE FUNCTION dbo.TimeSerial (@hrs int, @min int, @sec bigint)
RETURNS nvarchar(10)
AS
BEGIN
DECLARE @result nvarchar(10), @total bigint, @AMorPM nvarchar(2)
DECLARE @hours int, @minutes int, @seconds int
-- convert everything to seconds handling null params with isnull or coalesce
SET @total = IsNull(@sec,0) + IsNull(@min,0) * 60 + IsNull(@hrs,0) * 3600
SET @total = 86400 + @total % 86400 -- handle negative time relative to midnight
-- calculate the hour portion
SET @hours = 0
IF (@total >= 3600)
BEGIN
SET @hours = floor(@total/3600) % 24
SET @total = @total % 3600
END
-- set am/pm based on hours in HH format
SET @AMorPM = 'PM'
IF @hours < 12
BEGIN
SET @AMorPM = 'AM'
END
-- adjust hours to non-military time
IF @hours > 12 OR @hours = 0
BEGIN
SET @hours = abs(@hours - 12)
END
-- calculate the minutes and seconds portion
SET @minutes = 0
IF (@total >= 60)
BEGIN
SET @minutes = floor(@total/60)
SET @total = @total % 60
END
-- set seconds to remainder
SET @seconds = @total
SET @result = Cast(@hours As nvarchar(2)) + ':' + RIGHT('0'+Cast(@minutes As nvarchar(2)), 2)
SET @result = @result + ':' + RIGHT('0'+Cast(@seconds As nvarchar(2)),2) + @AMorPM
RETURN @result
END
As you will see in the code above, which hopefully speaks for itself as to what it is doing, we can do a little more than just format our time so we abstract out the need for extensive case when logic and give ourselves a handy utility function for our SQL toolkit.
Putting it in place with our original query, we can immediately simplify our syntax to this.
WITH TableByTime As (
SELECT Cast(DateName(hh, CallDateTime) As Int) As [Hr],
FLOOR(Cast(DateName(n, CallDateTime) As Int)/30) * 30 As [Mi],
Customer
FROM CustomerCalls (NoLock)
)
SELECT dbo.TimeSerial(Hr, Mi, 0) As [Hour],
Count(*)
FROM TableByTime
GROUP BY Hr, Mi
ORDER BY Hr, Mi
Aside from the TimeSerial function addition to the code, you will notice a better algorithm to get to group time in 30 minute buckets using floor which gets the lowest integer count of 30 in the number of minutes currently in our time. Since Microsoft SQL will typically do integer division on two numbers that are int datatypes this is probably unnecessary, but I like to be very deliberate in code for nothing else than I will remember what I was thinking when I look at it a year later. The premise here is that we will only ever get 0 or 1 from the division and then multiplying by 30 will give us the correct bucket.
Well we are close to being golden, but we still have a little fluff just to extract the hour and minute portions of time as we have to get as string then cast, so we could write functions for those as well; however, with Microsoft SQL Server, you can utilize ODBC canonical functions for { fn HOUR() } and { fn MINUTE() }.
dbo.TimeSerial({ fn HOUR(GETDATE()) }, FLOOR({ fn MINUTE(GETDATE()) } / 30) * 30, 0) AS HourBucket
As you see above, in combination with our user defined function for TimeSerial the ODBC canonical functions for HOUR and MINUTE make it very streamlined to get our hour bucket in one statement. For sorting purposes, it will probably still be a good idea to use the { fn HOUR() } in Hr column and { fn MINUTE() } in the Mi column in previous code using the common table expression we composed, then just use TimeSerial with those column values.
In conclusion, combining the flexibility of user defined functions and some of the tools in our SQL toolkit with Microsoft SQL Server, we can keep the user community happy with snappy time based reports. The common language runtime (CLR), which we didn't discuss here in detail, is another great means of adding user defined functionality. Anyway, happy coding.
References:
