Solved

# How to round based on a number range

Posted on 2013-01-11
421 Views
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
Question by:xbox360dp
• 4
• 2

LVL 73

Expert Comment

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

Author Comment

ID: 38768863
Exactly ... strange I know.
0

LVL 73

Expert Comment

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

LVL 73

Expert Comment

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

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

Author Comment

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

ID: 38769037
- try use CEIL:

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

LVL 73

Accepted Solution

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…