• Status: Solved
• Priority: Medium
• Security: Public
• Views: 434

# 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;
0
xbox360dp
• 4
• 2
1 Solution

Commented:
always round up to the nearest "5"  ?
0

Author Commented:
Exactly ... strange I know.
0

Commented:
:new.prsvf11c      :=
(FLOOR(((:new.prsprsc - :new.prsvf1c) / :new.prsprsc * 100) + 4 / 5) * 5) || '%';
0

Commented:
To summarize the key part: rounding "n" UP to the nearest "5"

FLOOR((n + 4) / 5) * 5
0

Author 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

Commented:
- try use CEIL:

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

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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 4
• 2
Tackle projects and never again get stuck behind a technical roadblock.