Solved

How to round based on a number range

Posted on 2013-01-11
7
427 Views
Last Modified: 2013-01-16
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
Comment
Question by:xbox360dp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 38768844
always round up to the nearest "5"  ?
0
 

Author Comment

by:xbox360dp
ID: 38768863
Exactly ... strange I know.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38768889
:new.prsvf11c      :=
        (FLOOR(((:new.prsprsc - :new.prsvf1c) / :new.prsprsc * 100) + 4 / 5) * 5) || '%';
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 38768901
To summarize the key part: rounding "n" UP to the nearest "5"

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

Author Comment

by:xbox360dp
ID: 38768934
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 38769037
- try use CEIL:

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

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38769066
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Not listening to where 1 43
Oracle perfomance issue. 4 49
Please explain Equi-join 3 44
Create a Calendar table 29 45
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question