Round currency values

Question

Round currency values

markjrouse

Mr or Mrs. 500

Points: 512
More actions
March 25, 2010 at 11:05 am

#231245

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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

Paul White SSC Guru Points: 150467 More actions · Answer 2

or:

select amount

from @sample

where amount = amount / $10000 * $10000

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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

andresito Ten Centuries Points: 1032 More actions · Answer 4

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

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

andresito Ten Centuries Points: 1032 More actions · Answer 6

That makes a lot of sense to me. Thanks for the feedback.

Paul White SSC Guru Points: 150467 More actions · Answer 7

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

markjrouse Mr or Mrs. 500 Points: 512 More actions · Answer 8

markjrouse

Mr or Mrs. 500

Points: 512

March 26, 2010 at 3:21 am

#1140214

Thanks a lot for your help

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

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

Paul White SSC Guru Points: 150467 More actions · Answer 10

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.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi