| Round values to 0,5,10,100 .. [message #623130] |
Fri, 05 September 2014 00:24  |
mmohsinaziz
Messages: 110
Registered: May 2012
|
Senior Member |
|
|
Dear Experts,
I need to round the values to nearest 0,5,10,50,100 and so on. Please help me to find out the output.
select 2 value from dual union all
select 3 from dual union all
select 4 from dual union all
select 8 from dual union all
select 15 from dual union all
select 25 from dual union all
select 30 from dual union all
select 48 from dual union all
select 65 from dual union all
select 85 from dual union all
select 92 from dual union all
select 94 from dual union all
select 103 from dual union all
select 116 from dual union all
select 122 from dual
Required Output is
VALUE REQUIRED VALUE
---------- --------------
2 0
3 5
4 5
8 10
15 10
25 50
30 50
48 50
65 50
85 100
92 100
94 100
103 100
116 100
122 100
Thanks for your time and efforts.
|
|
|
|
| Re: Round values to 0,5,10,100 .. [message #623132 is a reply to message #623130] |
Fri, 05 September 2014 00:33  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
As there is no rules for your target values then use CASE:
SQL> with data as (
2 select 2 value from dual union all
3 select 3 from dual union all
4 select 4 from dual union all
5 select 8 from dual union all
6 select 15 from dual union all
7 select 25 from dual union all
8 select 30 from dual union all
9 select 48 from dual union all
10 select 65 from dual union all
11 select 85 from dual union all
12 select 92 from dual union all
13 select 94 from dual union all
14 select 103 from dual union all
15 select 116 from dual union all
16 select 122 from dual
17 )
18 select value,
19 case
20 when value >= 75 then 100
21 when value >= 25 then 50
22 when value >= 7.5 then 10
23 when value >= 2.5 then 5
24 else 0
25 end new_val
26 from data
27 /
VALUE NEW_VAL
---------- ----------
2 0
3 5
4 5
8 10
15 10
25 50
30 50
48 50
65 50
85 100
92 100
94 100
103 100
116 100
122 100
15 rows selected.
[Updated on: Fri, 05 September 2014 00:33] Report message to a moderator |
|
|
|
| Re: Round values to 0,5,10,100 .. [message #623138 is a reply to message #623132] |
Fri, 05 September 2014 01:51 |
mmohsinaziz
Messages: 110
Registered: May 2012
|
Senior Member |
|
|
Dear Michel,
Thanks for your reply.
I think i am unable to explain my requirement. The point is to round the values to its nearest 0,5,10,50,100,150,200,250,300,350,400 and so on.
If I continue the previous example value 125 will be rounded to 150, 140 to 150, 165 to 200 and so on.
It will be difficult to cap the values.
Regards
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
