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
8: @WholeValue decimal(20,4) -- TODO: Set this to your field type.
10: RETURNS decimal(20,2)
13: -- Declare the return variable here
14: DECLARE @RoundedValue decimal(20,2)
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
25: -- Return the result of the function
26: RETURN @RoundedValue
Once the function is implemented it can be used in any view or select statement with the following:
2: dbo.GetRoundedTimePeriod(SUM(EmployeeTime)) AS EmployeeTime
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.