Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2008-12-24 : 06:39:27
|
| I have a table that contains all the refunds that were made against purchased items:ItemID Refund1 1002 1343 244 2005 223I have a query that retrieves all the items that have a rounded amount:select * from items where substring(cast(refund as varchar(10)), len(I.AmountCharged)-1, 2) = '00'Is there a way to rewrite this to be more performant and neater? (i.e. not to do a table scan and reduce the number of functions)ThanksHearty head pats |
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2008-12-24 : 06:40:16
|
| sorry - mistake:substring(cast(refund as varchar(10)), len(refund)-1, 2) = '00'Hearty head pats |
 |
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-12-24 : 06:51:47
|
quote:
Originally posted by Bex
sorry - mistake:substring(cast(refund as varchar(10)), len(refund)-1, 2) = '00'Hearty head pats
Is a rounded figure one which is divisible by 100 in your case ?? Your logic seems to say so. Instead use this,select * from items where refund%100=0 |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2008-12-24 : 06:54:35
|
| Yes, that is correct, and thank you, that works perfectly.Have a great christmas!Hearty head pats |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-12-24 : 06:56:59
|
quote:
Originally posted by Bex
Have a great christmas!Hearty head pats
Thanks, Merry Christmas :) |
|
|
|
|
|
|
Searching for rounded figures