March 25, 2010 at 11:05 am
I'm looking for a way to identify all round currency amounts in a given fileld. So if I have a table that has 92,543 reords in an a currency amount, is thee a ay of isolating amounts tht are round values, i.e. $6,000 or $150.
March 25, 2010 at 11:25 am
Sure... just test if it is evenly divisible by 1. The modulo operator (%) returns the remainder of a division operator, so if Amount % 1 = 0, you know it is an "even" amount.
declare @sample table (amount money)
insert into @sample
select 11.25 union all
select 2000 union all
select 345.67 union all
select 1 union all
select 45.01
select amount,amount%1 as test
from @sample
where amount%1 = 0
Want to see if the amount is even divisible by 100?
select amount,amount % 100 as test
from @sample
where amount % 100 = 0
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 25, 2010 at 11:31 am
or:
select amount
from @sample
where amount = amount / $10000 * $10000
March 25, 2010 at 11:44 am
Paul's solution will work. Just don't confuse it with the following code which will NOT work, in violation of the laws of algebra.
select amount
from @sample
where amount = $10000 * amount / $10000
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 25, 2010 at 2:28 pm
There are probably a lot of ways to do what you're wanting to accomplish. The guys who have posted here are much more experienced than I am, so I'm happy to be over-ruled... The way I have accomplished this in the past, at least in this example, is with a CAST: SELECT amount
FROM @sample
WHERE CAST(amount AS INT) = amount
March 25, 2010 at 2:49 pm
There's nothing wrong with your CAST solution, andresito. It works perfectly for getting rid of the spare change.
The reason(s) I used the modulo operator is that it is easy to test for tens, hundreds, thousands, or whatever (You could even use it to test for increments of $50 for example.), and it's very readable when you change from one measurement to another. Looking at the examples he gave, I wasn't sure at what level Mark wanted to test.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 25, 2010 at 4:07 pm
That makes a lot of sense to me. Thanks for the feedback.
March 25, 2010 at 10:36 pm
The Dixie Flatline (3/25/2010)
There's nothing wrong with your CAST solution, andresito. It works perfectly for getting rid of the spare change.
Au contraire....:-)
The rules on converting to an INTEGER depend on the data type, see CONVERT (Transact-SQL). In the case of converting MONEY to INTEGER, the value is rounded, not truncated - so $0.50 becomes 1.
Paul
March 26, 2010 at 3:21 am
Thanks a lot for your help
March 26, 2010 at 9:00 am
Paul, you are compelling me to up my game here in the forums. You will be hearing from my wife about the lack of attention she receives while I am exhaustively studying BOL before ever making another comment. 😀
But you are absolutely right and I was wrong, so thanks as usual for pointing it out.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 26, 2010 at 10:35 am
The Dixie Flatline (3/26/2010)
Paul, you are compelling me to up my game here in the forums. You will be hearing from my wife about the lack of attention she receives while I am exhaustively studying BOL before ever making another comment. 😀
:laugh: That made me smile...thanks.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply