sort-of rounding fractional numbers at the .5 mark
Given:
create table foo (
idno,
num1 number (4,2))
how do I select num1 where n > n.00 and < n.50 are rounded down to n.00, n.50 is left alone, and > n.51 and < n.99 are rounded to n+1?
I've tried a decode like:
select idno, decode(num1 - floor(num1), between 0.01 and .49, floor(num1), between .51 and .99, floor(num1) + 1, num1) from foo
create table foo (
idno,
num1 number (4,2))
how do I select num1 where n > n.00 and < n.50 are rounded down to n.00, n.50 is left alone, and > n.51 and < n.99 are rounded to n+1?
Basically the following
original rounded to
1.00 1.00
1.25 1.00
1.50 1.50
1.75 2.00
etc
The normal round function will not work here because it will convert 1.4 or 1.6 to 1.5, which is not what I want. I want everything from 1.01 to 1.49 to be rounded to 1.00, and everything from 1.51 to 1.99 to be rounded to 2:00, and 1.5 left alone.
I've tried a decode like:
select idno, decode(num1 - floor(num1), between 0.01 and .49, floor(num1), between .51 and .99, floor(num1) + 1, num1) from foo
0