Custom rounding logic in oracle sql

Hi everyone i am trying to create a custom logic for rounding off a decimal as specified in the rules.

The rule states that any decimal whose value is in between .01 to .05 should have .05 value. Any value which falls between .06 to .09 should be equal to .10.

For example

1.05 => 1.05
1.84 => 1.85
1.06 => 1.10
1.79 => 1.80

I tried to play with the formula specified in oracle website here. But it didn't work see below code

select 
round(1.05 * power(10,2) + .05) * power(10,-2) as Rounding1
, round(1.84 * power(10,2) + .05) * power(10,-2) as Rounding2
, round(1.06 * power(10,2) + .05) * power(10,-2) as Rounding3
, round(1.79 * power(10,2) + .05) * power(10,-2) as Rounding4
from dual;

Any ideas on how this can be implemented?

1 Answer

  1. Lee- Reply

    2019-11-14

    I think this logic does what you want:

    select ceil(num * 20) / 20 from tablename 
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>