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

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

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

Kids Activities

I find myself often in a situation where it's some morning, I have the kids for the afternoon, and I'm not sure what to do with them. We could go to a movie, or play Legos, but living near Washington, DC, I want the kids to love the museums as much as I do, or to see what else is going on. This Sunday, while my wife was travelling, I took the kids to the Chocolate Festival in Old Town Fairfax. I didn't even know there wad an Old Town Fairfax, much less a chocolate festival. It was okay overall, but the best was seeing any type of chocolate you could imagine, and letting the kids pick something for themselves and their teacher. For finding cheap or free stuff going on nearby with the kids, I have to say About.com has consistently been the best. I tried si.edu (the Smithsonian Website) which is also good, but a little hard to navigate, partly because they have so much going on. At About I did a search of what to do with my kids this weekend, and a bunch of items came ...
Post a Comment
Read more