Finding values with cents...

Question

Post reply

Finding values with cents...

Tracie Ann Williams

Old Hand

Points: 331
More actions
February 1, 2007 at 9:16 am

#122868

Can't seem to be able to figure out how to find values in a column of type money that have cents and have not been rounded to a whole dollar...
Appreciate the help and have a great day...

Viewing 15 posts - 1 through 15 (of 19 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

DECLARE @Mon AS Money

SET @Mon = 1.23

SELECT 1 WHERE @Mon = CONVERT(INT, @Mon)

SET @Mon = 2

SELECT 1 WHERE @Mon = CONVERT(INT, @Mon)

David McFarland SSChampion Points: 11815 More actions · Answer 2

The method I came up with is the same basic concept, and I have no idea whether it would be faster or slower than Ninja's solution (I'm guessing they'd be close to the same, but test away).

WHERE Round(col_name,0) <> col_name

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

No clue which one is fastest. Make a test, run it and post the results then we'll talk about it .

Tracie Ann Williams Old Hand Points: 331 More actions · Answer 4

Thanks - seems so obvious now...

Just an ad-hoc query on small amount of data, so used the round - easiest to implement and not really concerned about performance...

Thanks again!

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

Not much difference

DUMP TRAN dbName WITH NO_LOG

GO

CheckPoint

DECLARE @Counter integer

SET @Counter = 1

WHILE @Counter <= 50000 -- 0:00:27 seconds

BEGIN

DECLARE @Mon AS Money

SET @Mon = 1.23

SELECT 1 WHERE @Mon = CONVERT( integer, @Mon)

SET @Mon = 2

SELECT 1 WHERE @Mon = CONVERT( integer, @Mon)

SET @Counter = @Counter + 1

END

-----------------------------------------------------------------------------------------

DUMP TRAN dbName WITH NO_LOG

GO

CheckPoint

DECLARE @Counter integer

SET @Counter = 1

WHILE @Counter <= 50000 -- 0:00:28 seconds

BEGIN

DECLARE @Mon AS Money

SET @Mon = 1.23

SELECT 1 WHERE @Mon <> ROUND( @Mon, 0)

SET @Mon = 2

SELECT 1 WHERE @Mon <> ROUND( @Mon, 0)

SET @Counter = @Counter + 1

END

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

The round seems to win constantly on my slow PC :

USE SSC

GO

DUMP TRAN SSC WITH NO_LOG

GO

CheckPoint

DECLARE @Start AS Datetime

DECLARE @Bitbucket AS int

DECLARE @Counter integer

SET @Counter = 1

SET @Start = GETDATE()

WHILE @Counter <= 500000 -- 8156 miliseconds

BEGIN

DECLARE @Mon AS Money

SET @Mon = 1.23

SELECT @Bitbucket = 1 WHERE @Mon = CONVERT( integer, @Mon)

SET @Mon = 2

SELECT @Bitbucket = 1 WHERE @Mon = CONVERT( integer, @Mon)

SET @Counter = @Counter + 1

END

PRINT DATEDIFF(ms, @Start, GetDate())

-----------------------------------------------------------------------------------------

DUMP TRAN SSC WITH NO_LOG

GO

CheckPoint

DECLARE @Bitbucket AS int

DECLARE @Counter integer

DECLARE @Start AS Datetime

SET @Counter = 1

SET @Start = GETDATE()

WHILE @Counter <= 500000 -- 8080 miliseconds

BEGIN

DECLARE @Mon AS Money

SET @Mon = 1.23

SELECT @Bitbucket = 1 WHERE @Mon <> ROUND( @Mon, 0)

SET @Mon = 2

SELECT @Bitbucket = 1 WHERE @Mon <> ROUND( @Mon, 0)

SET @Counter = @Counter + 1

END

PRINT DATEDIFF(ms, @Start, GetDate())

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 7

One more way to do this, but I don't see any speed advantage on my machine. All three methods are 37 seconds. Although % is typically an integer operation, it returns the correct results here.

DECLARE

@Counter integer

SET @Counter = 1

WHILE

@Counter <= 50000

BEGIN

DECLARE @Mon AS Money

SET @Mon = 1.23

SELECT 1 WHERE @Mon % 1 > 0

SET @Mon = 2

SELECT 1 WHERE @Mon % 1 > 0

SET @Counter = @Counter + 1

END

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Use the bitbucket method... that way you won't have to select and display any data. That's why I can run half a million iterations in 8 seconds on a 1.3 Ghz CPU.

Adrian Nichols-360275 SSCertifiable Points: 7694 More actions · Answer 9

I had a similar problem which I solved with:

WHERE CAST((colName * 100) as INT) % 100 != 0

No performance issues either.

Ade

A Freudian Slip is when you say one thing and mean your mother.
For detail-enriched answers, ask detail-enriched questions...[/url]

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 10

I used the scripts supplied and ran 3 iterations on a number of servers. It seems that server load and hardware also have something to do with the results.

hp ml-370, dual 3.0 Gh Xeon,1.5 Gb RAM, 2 SQL 2000 instances, various support applications - users < 50

64686 ms - convert

64703 ms - round

64876 ms - convert

73936 ms - round

65046 ms - convert

64953 ms - round

ibm e-series 330, PIII 1.3 Gh, 1.0 Gb RAM, 1 SQL 2000 instance, production monitoring - users 50-100

97110 ms - convert

108046 ms - round

97530 ms - convert

105796 ms - round

95330 ms - convert

101636 ms - round

hp dl-580, quad 2.8 Gh Xeon, 4 Gb RAM, 1 SQL 2000 instance - full production load "a" - 200+ users

17986 ms - convert

17420 ms - round

18173 ms - convert

18046 ms - round

18170 ms - convert

18220 ms - round

hp dl-380 g3, dual 3.0 Gh Xeon, 4 Gb RAM, 1 SQL 2000 instance, 1 SQL 2005 instance, various support applications - users < 50

4826 ms - convert

4876 ms - round

4763 ms - convert

4826 ms - round

4890 ms - convert

5046 ms - round

hp dl-580, quad 2.8 Gh Xeon, 4 Gb RAM, 1 SQL 2000 instance - full production load "b" - 600+ users

7940 ms - convert

7673 ms - round

7750 ms - convert

7810 ms - round

8203 ms - convert

9453 ms - round

hp dl-580 g3, dual 3.0 Gh Xeon, 8 Gb RAM, 1 SQL 2000 instance, testing, no load - users < 50

9330 ms - convert

9296 ms - round

8966 ms - convert

9030 ms - round

9000 ms - convert

9123 ms - round

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Thanx for this extensisve testing. I think we can conclude that those 2 methods are pretty much tied up.

Anyhow just make sure you retest this on your server if you have a much bigger load of data.... just in case you have a constant different in any cases.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 12

After lunch I'll have some time and give things a whirl with 250,000 iterations on the same servers ... after all its Friday and I need to relax and have some fun !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Just make sure you run it with the bit bucket... 250 000 results window can actually kill your local machine .

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 14

I used the scripts supplied and ran 3 iterations on a number of servers. It seems that server load and hardware also have something to do with the results.

The first 3 iterations were for 500,000. The next 3 iterations were for 2,500,000.

hp ml-370, dual 3.0 Gh Xeon,1.5 Gb RAM, 2 SQL 2000 instances, various support applications - users < 50

-------

500,000

-------

64686 ms - convert

64703 ms - round

64876 ms - convert

73936 ms - round

65046 ms - convert

64953 ms - round

---------

2,500,000

---------

351690 ms - convert

322580 ms - round

326143 ms - convert

324456 ms - round

325393 ms - convert

329360 ms - round

ibm e-series 330, PIII 1.3 Gh, 1.0 Gb RAM, 1 SQL 2000 instance, production monitoring - users 50-100

-------

500,000

-------

97110 ms - convert

108046 ms - round

97530 ms - convert

105796 ms - round

95330 ms - convert

101636 ms - round

---------

2,500,000

---------

582546 ms - convert

583940 ms - round

603670 ms - convert

531970 ms - round

486516 ms - convert

459513 ms - round

hp dl-580, quad 2.8 Gh Xeon, 4 Gb RAM, 1 SQL 2000 instance - full production load "a" - 200+ users

-------

500,000

-------

17986 ms - convert

17420 ms - round

18173 ms - convert

18046 ms - round

18170 ms - convert

18220 ms - round

---------

2,500,000

---------

39656 ms - convert

39843 ms - round

38670 ms - convert

38653 ms - round

39326 ms - convert

39843 ms - round

hp dl-380 g3, dual 3.0 Gh Xeon, 4 Gb RAM, 1 SQL 2000 instance, 1 SQL 2005 instance, various support applications - users < 50

-------

500,000

-------

4826 ms - convert

4876 ms - round

4763 ms - convert

4826 ms - round

4890 ms - convert

5046 ms - round

---------

2,500,000

---------

27003 ms - convert

35563 ms - round

24923 ms - convert

24280 ms - round

24170 ms - convert

26126 ms - round

hp dl-580, quad 2.8 Gh Xeon, 4 Gb RAM, 1 SQL 2000 instance - full production load "b" - 600+ users

-------

500,000

-------

7940 ms - convert

7673 ms - round

7750 ms - convert

7810 ms - round

8203 ms - convert

9453 ms - round

---------

2,500,000

---------

48423 ms - convert

41110 ms - round

41216 ms - convert

42186 ms - round

41606 ms - convert

42923 ms - round

hp dl-580 g3, dual 3.0 Gh Xeon, 8 Gb RAM, 1 SQL 2000 instance, testing, no load - users < 50

-------

500,000

-------

9330 ms - convert

9296 ms - round

8966 ms - convert

9030 ms - round

9000 ms - convert

9123 ms - round

---------

2,500,000

---------

55093 ms - convert

48126 ms - round

61340 ms - convert

46673 ms - round

45373 ms - convert

45746 ms - round

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."