CODEWITHSUNDEEP
sqlsql-servert-sqldecimalrounding
Ayman
Ayman

Reputation: 872

Rounding Values in sql server

I'm looking to round values like below

10.5  -> 11.00
28.35 -> 28.50
17.85 -> 18.00

Condition is if value < 50 then round to .50 else it will round to 1.

How can i apply in SQL.

Upvotes: 2

Views: 104

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

Try this logic:

SELECT
    value AS old_value,
    CASE WHEN value < 50
         THEN CEILING(value*2) / 2
         ELSE CEILING(value) END AS new_value
FROM yourTable;

After reviewing your comment, I actually stand by the logic in this query. Here are the results I generated while testing:

screen shot of test data

Demo

Note that 10.5 maps to itself, not to 11.0. The reason for this is that you have defined the 0.5 level as a stop gap for rounding. It makes no sense that other numbers should round to the nearest 0.5, and stop there, but 10.5 should keep going to 11.0.

Upvotes: 2

Related Questions