Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to round based on a number range

Posted on 2013-01-11
7
Medium Priority
?
429 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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…
This video shows how to recover a database from a user managed backup

722 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