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

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 d...
6 comments
Read more

Using an Array of Objects in C++

 I've been programming for years (over 35 at this point, which is crazy  to think about). My career right now is much more Software Architecture, and much less Software Developer, but I still get some time to write out GraphQL APIs in TypeScript, Vue 3 UIs, GitLab pipelines, and just generally making "big" decisions and helping make them a reality. It's nice every now and then to come across different articles and ideas that get me to remember life in college when I was using C++. Who would have thought C++ was the "hot new thing" right now (though I suppose it's more like Rust and Go, both great languages as well). One of the things I find frustrating with most technical posts is where they focus on the "how do I build an app" and not so much on "how do I do this one slightly useful thing". I figured I'd throw one together what was front of mind, using user attributes for permissions (i.e., Attribute Based Access Control - ABAC) ...
Post a Comment
Read more

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...
8 comments
Read more