T-SQL: Rounding up to quarters

user_1075106 0
Viewed 1281

In a report where I output hours on service contracts, I need to round my contract hours up.

Currently I'm getting my "Contract Hours" field from Navision. I add 10% (company policy) on these hours and this results in a number of hours which we can use to bill that customer.

However by doing this I get really weird contract hours (10.04, 28.82, 9.57, ...) Now there is an idea where we round up these numbers to .25, .50, .75 or .00 But it needs to ALWAYS round up.

10.04 should result in 10.25

28.82 should result in 29.00

9.57 should result in 9.75

I have tried this by doing ROUND(@value/25,2)*25, but this doesn't give me the necessary result.

  • 10.04 becomes 10 (when I expect 10.25)
  • 28.82 becomes 28.75 (when I expect 29.00)
  • 9.57 becomes 9.50 (when I expect 9.75)

Could anybody give me some extra tips on how to handle this please?

rounding tsql sql-server
1 Answers

You can use CEILING function instead of ROUND, it will "round up" your numbers if between two others, even if they are "closer" to the smaller number.

Your overall logic to round to "quarters" looked good, but the ROUND function will choose the closest one, not the "next greater one".

Also, you could play with round with the second parameter to fit your needs, but you can't with ceiling, so you will have to change the number crunching a bit.

So, in your case : 

CEILING(@value * 4) / 4

should do.

EDIT (thanks @HABO) :

In case your handling negative numbers, keep in mind that this will still round up (-10.3 will become -10.25). If you need to round down in this case to make it looks like when it is positive (i.e. round -10.3 to -10.5), you can handle both cases with this variation : 

SIGN(@value) * CEILING(ABS(@value) * 4) / 4

Additional considerations :

  • To round down to the previous quarter, just replace CEILINGby FLOOR
user_4792510
Related