Crossed Logic: Group By Time: TimeSerial Makes A Return To Microsoft SQL 2005

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: