February 14, 2013 at 9:19 am
I know I've seen the answer to this before, but my Google fu fails me (I'm probably searching the wrong terms).
I'm trying to find values in a table that have greater than 2 digits after the decimal point. We have a couple of errant pennies showing up because of addition of .003 and .006 etc. But when I come up with code to look for the percentages of pennies, it doesn't find the records I expect it to. I have at least 9 records to use as a base comparison and none of them are coming up. Then I realized my conversion was cutting off the 3rd and 4th digit of money.
Here's some sample code to show what's happening.
CREATE TABLE #MoneyTest (MyID INT IDENTITY(1,1), OrderNumber INT, OrderTotal MONEY);
INSERT INTO #MoneyTest (OrderNumber, OrderTotal)
VALUES (134855, 215.00),(8547566, 103.002),(574163, 25.0004),(469578,103.5500);
SELECT OrderNumber, OrderTotal, CONVERT(VARCHAR(10),OrderTotal) AS Test
FROM #MoneyTest;
I need to locate the middle two records. But since the code is dropping off that 2 and 04 from the amounts, my code can't find it.
SELECT OrderNumber, OrderTotal, CONVERT(INT,SUBSTRING( CONVERT(VARCHAR(10),OrderTotal),CHARINDEX('.',CONVERT(VARCHAR(10),OrderTotal)) + 1, 4))
FROM #MoneyTest
WHERE CONVERT(INT,SUBSTRING( CONVERT(VARCHAR(10),OrderTotal),CHARINDEX('.',CONVERT(VARCHAR(10),OrderTotal)) + 1, 4)) > 0
Can someone point me to the topics where this issue has already been discussed? Or offer me other options?
I'd greatly appreciate the assist.
February 14, 2013 at 9:29 am
Maybe this?
SELECT ordertotal
FROM #MoneyTest
where ordertotal - round(ordertotal, 2) > 0
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 14, 2013 at 9:48 am
Abu Dina (2/14/2013)
Maybe this?
SELECT ordertotalFROM #MoneyTest
where ordertotal - round(ordertotal, 2) > 0
Oh. You want me to use the SIMPLE solution. 😀
<headdesk>
Really? I had to make things THAT difficult for myself?
Thanks, BTW. That is what I needed. I don't know why I felt the need to over-engineer my problem.
February 14, 2013 at 9:56 am
I don't know why I felt the need to over-engineer my problem.
LOL.... I do it all the time that I keep having to remind myself with regular mental notes 😛
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply