Rounding to quarter hours with a solution in SQL Server

A co-worker asked me the other day about an application where they want to sum values and round them to a quarter of a while number. In this case we had a time entry tool where users entered time in any format, not specifically quarter of hours, but it needed to be summed and rounded to the quarter hour.

For example, I have a number 6.6143 and it needs to be rounded to 6.5. Looking around I just couldn’t find an example where pure math was used, the best were some general rounding to different values using a case statement.

In the end I took this approach, throwing together a quick SQL Server function to get the results. the solution essentially takes the number, removes the whole digits, rounds to two decimals, then checks if those two decimals all in a range.

   1: -- =============================================

   2: -- Author:        Tom Leary

   3: -- Create date:   8/13/2009

   4: -- Description:   Rounds the given number to a quarter of an hour.

   5: -- =============================================

   6: CREATE FUNCTION dbo.GetRoundedTimePeriod

   7: (

   8:       @WholeValue decimal(20,4) -- TODO: Set this to your field type.

   9: )

  10: RETURNS decimal(20,2)

  11: AS

  12: BEGIN

  13:       -- Declare the return variable here

  14:       DECLARE @RoundedValue decimal(20,2)

  15:  

  16:       -- Add the T-SQL statements to compute the return value here

  17:       SET @RoundedValue = CASE 

  18:                               WHEN (Round(@WholeValue, 2) - Floor(@WholeValue)) BETWEEN 0 AND .12 THEN Floor(@WholeValue)

  19:                               WHEN (Round(@WholeValue, 2) - Floor(@WholeValue)) BETWEEN .13 AND .37 THEN Floor(@WholeValue) + 0.25

  20:                               WHEN (Round(@WholeValue, 2) - Floor(@WholeValue)) BETWEEN .38 AND .62 THEN Floor(@WholeValue) + 0.50

  21:                               WHEN (Round(@WholeValue, 2) - Floor(@WholeValue)) BETWEEN .63 AND .87 THEN Floor(@WholeValue) + 0.75

  22:                               ELSE Floor(@WholeValue) + 1

  23:                         END

  24:  

  25:       -- Return the result of the function

  26:       RETURN @RoundedValue

  27:  

  28: END

Once the function is implemented it can be used in any view or select statement with the following:

   1: SELECT

   2:       dbo.GetRoundedTimePeriod(SUM(EmployeeTime)) AS EmployeeTime

   3: FROM

   4:       EmployeeTimeSheet

Obviously this function could be implemented in .NET in some common utilities class as well, or really any language.

Any thoughts? If you know how to do this mathematically I’d love to hear it.

Peace,

+Tom