Solved

Rounding down to the nearest 5

Posted on 2013-05-15
11
446 Views
Last Modified: 2013-05-16
Gurus,                                                    
                                                         
I need some help rounding down to the nearest 5.          
                                                         
Example                                                  
                                                         
before:                                                  
                                                         
r-price = 100.00                                          
s-price = 42.00                                          
                                                         
percent saved % = 58                                      
dollar amount saved = 58.00                              
                                                         
after:                                                    
                                                         
r-price = 100.00                                          
s-price = 42.00                                          
                                                         
percent saved % = 55                                      
dollar amount saved = 55.00                              
                                                         
So if the value is 51 - 54 round to 50 and 56-59 round 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.Prsvf17c := (:New.Prsprsc - :new.prsvf1c);                            
:New.Prsvf11c := ((:New.Prsprsc - :new.prsvf1c)/:new.prsprsc *100)||'%';
                                                                                                       
END;  
0
Comment
Question by:xbox360dp
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:pjevin
ID: 39169115
Round(:New.Prsvf11c*2/10)/2*10
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39169172
ceil((:New.Prsprsc-4)/5)*5
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39169174
>>Round(:New.Prsvf11c*2/10)/2*10

I found that with Google as well.
However,  59 gives you 60 not 55.
0
 
LVL 3

Expert Comment

by:pjevin
ID: 39169231
Ah, sorry I missed the rounding down part, thought it was just the nearest 5.. just subtract 2 first.

Round((:New.Prsvf11c-2)*2/10)/2*10
0
 
LVL 3

Expert Comment

by:pjevin
ID: 39169252
2.5 if you have a possibility of decimals, but ceil or floor or whatever you want works too, just not compatible with all databases.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 73

Expert Comment

by:sdstuber
ID: 39169283
>>> just not compatible with all databases.

well, this was asked in the Oracle topic area, but even if we extend to other platforms, out of curiosity, which ones were you thinking of that don't have a ceiling function or equivalent?
0
 
LVL 3

Expert Comment

by:pjevin
ID: 39169333
It had a "SQL Query Syntax" tag as well... Oracle didn't in early versions, and most other DBs until recently (Informix did not until 11 which is what I was thinking of specifically, and surprisingly even MS Access).  Back in the day, people used to have to do math!
0
 

Author Comment

by:xbox360dp
ID: 39169556
Sdstuber,

Thanks for the feedback ... but how do I incorporate your examples in the trigger I have in my question?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39169701
what column do you need rounded?

put something like this in your trigger where xxxxx is the column you need rounded


:new.xxxxxx := ceil((:New.xxxxx-4)/5)*5 ;
0
 

Author Comment

by:xbox360dp
ID: 39169748
So ..

:New.Prsvf19c := (ceil(((:new.prsprsc - :new.prsvf1c-4) / :new.prsprsc * 100)  / 5) * 5) || '%'; ??
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39169852
if you say so

this is the first time you've posted anything about the Prsvf19c  column.


what value will have 58 that you need rounded down to 55?

that's the one you need to put in the xxxxxx  here.


 ceil((xxxxxx-4)/5)*5 ;


then assign that to whatever you column it belongs in,  with '%' appended if you want.

basically, the math is already defined for you.
but only only can decide which columns that math applies to
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now