How to round based on a number range

Gurus,

I need some help rounding based on a number range.

Example1:

Retail Price: 100.00
Sale Price: 42.00

Dollar % Saved: 58

The logic here is that if the % saved is between 55 - 59 then
Dollar % Saved = 60

Example2:

Retail Price: 100.00
Sale Price: 48.00

Dollar % Saved: 52

The logic here is that if the % saved is between 51 - 54 then
Dollar % Saved = 55

How do I accomplish this in my trigger?

create or replace
TRIGGER SALE_CALCULATIONS BEFORE
  INSERT OR
  UPDATE ON Prs FOR EACH Row
 
BEGIN
:New.Prsvf11c := ((:New.Prsprsc - :new.prsvf1c)/:new.prsprsc *100)||'%';
END;
xbox360dpAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
the formulas are correct but I  messed up the () putting them into your trigger code.

you can verify the formulas with this

    select n,FLOOR((n + 4) / 5) * 5, ceil(n/5)*5 from (
    select level-1 n from dual connect by level < 103) ;


try this...


CREATE OR REPLACE TRIGGER sale_calculations
    BEFORE INSERT OR UPDATE
    ON prs
    FOR EACH ROW
BEGIN
    :new.prsvf11c :=
        (FLOOR((((:new.prsprsc - :new.prsvf1c) / :new.prsprsc * 100) + 4) / 5) * 5) || '%';
END;

or

CREATE OR REPLACE TRIGGER sale_calculations
    BEFORE INSERT OR UPDATE
    ON prs
    FOR EACH ROW
BEGIN
    :new.prsvf11c :=
        (ceil(((:new.prsprsc - :new.prsvf1c) / :new.prsprsc * 100)  / 5) * 5) || '%';
END;
0
 
sdstuberCommented:
always round up to the nearest "5"  ?
0
 
xbox360dpAuthor Commented:
Exactly ... strange I know.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
:new.prsvf11c      :=
        (FLOOR(((:new.prsprsc - :new.prsvf1c) / :new.prsprsc * 100) + 4 / 5) * 5) || '%';
0
 
sdstuberCommented:
To summarize the key part: rounding "n" UP to the nearest "5"

FLOOR((n + 4) / 5) * 5
0
 
xbox360dpAuthor Commented:
Sdstuber,

Using the your code ... if the % saved is 52 I get "56" instead of "55" ... if the % saved is 58 I get "62" instead of "60".
0
 
OP_ZaharinCommented:
- try use CEIL:

select CEIL(<numberhere>/5)*5 from dual
0
All Courses

From novice to tech pro — start learning today.