OraQA

The following query rounds a number up to the next multiplier.

Assumptions: num can be any positive number. multiplier can be any positive integer.

SQL> var num number;
SQL> var multiplier number;
SQL> exec :num := 19; :multiplier := 20;

PL/SQL procedure successfully completed.

SQL> SELECT   TRUNC (:num / :multiplier) * :multiplier
  2         + DECODE (MOD (:num, :multiplier), 0, 0, :multiplier) r
  3    FROM DUAL;

         R
----------
        20

And the following query does the opposite, it rounds down:

SQL> exec :num := 45; :multiplier := 40;

PL/SQL procedure successfully completed.

SQL> SELECT TRUNC (:num / :multiplier) * :multiplier r
  2    FROM DUAL;

         R
----------
        40

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.