Solved

How to round based on a number range

Posted on 2013-01-11
7
426 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
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in creating a view. 8 34
Comparison query - 4 columns 9 42
sql server store procedure contains temp tables need to convert oracle? 3 37
Excess Redo 3 32
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

756 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