Another way to solve this?
SELECT (112 - MOD (112, 25))
+ (CASE
WHEN MOD (112, 25) BETWEEN 0 AND 12.49
THEN 0
ELSE 25
END) less_then_25,
(112.5 - MOD (112.5, 25))
+ (CASE
WHEN MOD (112.5, 25) BETWEEN 0 AND 12.49
THEN 0
ELSE 25
END) greater_then_25
FROM DUAL
Thanks,
On Fri, Feb 10, 2012 at 11:42 PM, ddf <[email protected]> wrote:
>
>
> On Feb 10, 10:44 am, Andrej Hopko <[email protected]> wrote:
> > Hi,
> > just simple suggestion - what about dividing number by 25 (which is step
> > for your ROUND), rounding the decimal to nearest integer (standard ROUND
> > function should by sufficient) and then multiplying integer back with 25
> > - the solution on level of two years programming experience at high
> > school :D
> >
> > hope I was of some help
> >
> > btw. although oracle has ROUND with second paramter - as a grade of
> > ROUND, it works only for integer grades of 10 to round:
> >
> > SELECT ROUND(113.34,-1.5) c1
> > , ROUND(113.34,-LOG(10, 25)) c2
> > , ROUND(113.34,-1.5) c2
> > , ROUND(113.34,-2) c2
> > FROM DUAL
> > ;
> >
> > C1 C2 C2 C2
> > ---------------------- ---------------------- ----------------------
> > ----------------------
> > 110 110 110 100
> >
> > I found this interesting news :)
> >
> > best regards
> > hoppo
> >
> > On 10. 2. 2012 18:13, swaroop gowda wrote:
> >
> >
> >
> > > Hi All,
> > > I have total amount like 110.01, 113.34, 126.01, 145.54, 155.01 etc
> > > but I need to round this 100 or 125 or 150 etc.
> >
> > > Please find my requirement below,
> > > 1) I need to round to 100 if the total is between 100 to 112.
> > > 2) I need to round to 125 if the total is between 113 to 125.
> > > 3) I need to round to 125 if the total is between 116 to 137.
> > > 4) I need to round to 150 if the total is between 138 to 150.
> > > 5) I need to round to 150 if the total is between 151 to 167.
> > > 5) I need to round to 200 if the total is between 168 to 200.
> >
> > > You help is greatly appreciated.
> > > --
> > > Thanks & Regards
> > > Swaroop Thailuru Swamy
> > > Ph: 713-392-1571
> >
> > > --
> > > You received this message because you are subscribed to the Google
> > > Groups "Oracle PL/SQL" group.
> > > To post to this group, send email to [email protected]
> > > To unsubscribe from this group, send email to
> > > [email protected]
> > > For more options, visit this group at
> > >http://groups.google.com/group/Oracle-PLSQL?hl=en- Hide quoted text -
> >
> > - Show quoted text -
>
> I will agree this problem can be solved outside of PL/SQL and is
> probably better suited to one of the more generic Oracle newsgroups.
> However solutions can be offered using both:
>
> SQL> create table round_amt(
> 2 amt number(9,2)
> 3 );
>
> Table created.
>
> SQL>
> SQL> insert all
> 2 into round_amt
> 3 values(110.25)
> 4 into round_amt
> 5 values(120.25)
> 6 into round_amt
> 7 values(113.25)
> 8 into round_amt
> 9 values(117.25)
> 10 into round_amt
> 11 values(116.25)
> 12 into round_amt
> 13 values(190.25)
> 14 into round_amt
> 15 values(180.25)
> 16 into round_amt
> 17 values(160.25)
> 18 into round_amt
> 19 values(150.25)
> 20 into round_amt
> 21 values(200.25)
> 22 select * From dual;
>
> 10 rows created.
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> --
> SQL> -- Straight SQL solution
> SQL> --
> SQL>
> SQL> select amt,
> 2 case when trunc(amt) between 110 and 112 then 110
> 3 when trunc(amt) between 113 and 137 then 125
> 4 when trunc(amt) between 138 and 167 then 150
> 5 when trunc(amt) between 168 and 200 then 200 end
> rounded_amt
> 6 from round_amt;
>
> AMT ROUNDED_AMT
> ---------- -----------
> 110.25 110
> 120.25 125
> 113.25 125
> 117.25 125
> 116.25 125
> 190.25 200
> 180.25 200
> 160.25 150
> 150.25 150
> 200.25 200
>
> 10 rows selected.
>
> SQL>
> SQL> --
> SQL> -- PL/SQL solution
> SQL> --
> SQL>
> SQL> declare
> 2 cursor get_amt is
> 3 select amt, trunc(amt) t_amt
> 4 from round_amt;
> 5 begin
> 6 for i in get_amt loop
> 7 if i.t_amt between 100 and 112 then
> 8 dbms_output.put_line(i.amt||' rounded is
> 100');
> 9 elsif i.t_amt between 113 and 137 then
> 10 dbms_output.put_line(i.amt||' rounded is
> 125');
> 11 elsif i.t_amt between 138 and 167 then
> 12 dbms_output.put_line(i.amt||' rounded is
> 150');
> 13 elsif i.t_amt between 168 and 200 then
> 14 dbms_output.put_line(i.amt||' rounded is
> 200');
> 15 else
> 16 dbms_output.put_line(i.amt||' is out of
> range');
> 17 end if;
> 18
> 19 end loop;
> 20
> 21 end;
> 22 /
> 110.25 rounded is 100
> 120.25 rounded is 125
> 113.25 rounded is 125
> 117.25 rounded is 125
> 116.25 rounded is 125
> 190.25 rounded is 200
> 180.25 rounded is 200
> 160.25 rounded is 150
> 150.25 rounded is 150
> 200.25 rounded is 200
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> David Fitzjarrell
>
> --
> You received this message because you are subscribed to the Google
> Groups "Oracle PL/SQL" group.
> To post to this group, send email to [email protected]
> To unsubscribe from this group, send email to
> [email protected]
> For more options, visit this group at
> http://groups.google.com/group/Oracle-PLSQL?hl=en
>
--
Regards,
Jignesh Makwana,
+919892500936
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
