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

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
Asked:
xbox360dp
  • 4
  • 2
1 Solution
 
sdstuberCommented:
always round up to the nearest "5"  ?
0
 
xbox360dpAuthor Commented:
Exactly ... strange I know.
0
 
sdstuberCommented:
:new.prsvf11c      :=
        (FLOOR(((:new.prsprsc - :new.prsvf1c) / :new.prsprsc * 100) + 4 / 5) * 5) || '%';
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
 
sdstuberCommented:
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

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