Skip to main content

Rounding to quarter hours with a solution in SQL Server

Quarter before nine by ~rodneymarin 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

Labels:

Comments

Post a Comment

Popular posts from this blog

Advantages and Disadvantages of Using Microsoft Access

I've answered this question in some form or another far more times than I care to count.  Most often it's a question of "why do I need a fancy Web application when I can just build this myself in two days in Access.  I mean, the data's already in Excel."  So I figured I'd post out what I threw together, I know I've missed some points. Overview Microsoft Access is an ideal solution for relatively small datasets and a limited number of users. From the Microsoft Web site: “As a desktop database, Access is well suited for small, departmental applications. These applications may start as one user’s project. For example, an employee realizes that productivity can be increased if a paper-based process is automated with an Access application. Other users in the department recognize that they can take advantage of the application if additional features are added. As more features are added, more employees run the application. As time goes by, more and more Access
9 comments
Read more

Red-Gate SQL Compare

Every now and then I come across a program that becomes so ingrained in my daily work that I hardly know how I'd get by without it.  I'll probably break down a couple over the next few days, but for database work, I have never found anything as good as Red Gate's SQL Compare and SQL Data Compare .  Essentially these tools let you compare two SQL Server databases (all objects, users, permissions, functions, diagrams, anything) and update changes to whichever database you want.  This is amazingly useful for deploying database changes to a test or production environment (do it to production with ridiculous care, even though it will generate a SQL Script for you and run all updates in one transaction), and making sure everything is synchronized. For releases we can just generate the compare script, confirm that the changes match the updates we want to go out, and store it all in one place with the release details.  This is true for both the structure and the data, to
6 comments
Read more

Beryllium Spheres

I'm sitting here at home watching The Shadow , easily one of the best movies made based on one of the best old time radio shows.  I hadn't picked up on this earlier, but the weapon used to destroy the city is none other than the same power source used to power the NSEA Protector in Galaxy Quest . I never knew Beryllium was so cool.  Now I want a sphere of my own. Anyone know of other places Beryllium Spheres are mentioned? Peace, +Tom
6 comments
Read more