Query for decimal places 2

[Bluejay07]

Hi,

I am looking for a query where I need to determine if a specific decimal place is greater than zero.

Example.
3.450
4.835
2.764
9.882

If we take the above numbers, I would want to query based on the third decimal position.

The results should be:
4.835
2.764
9.882

Any help would be greatly appreciated.

 

[gmmastros]

Here's how I would approach this....

First multiply the number by 100. Then, take the floor of the value (this will round down to the nearest integer). Then, divide by 100.

In the first example, 3.450

Multiple by 100 = 345.0
Floor = 345.0
divide by 100 = 3.45

Since this is the same number you started with, filter it out.

Now for 4.835
Multiply by 100 = 483.5
Floor = 483
divide by 100 = 4.83

Since this is NOT the same number, include in your results.

Full Example:

Declare @Temp Table(Data Decimal(20,3))

Insert Into @Temp Values(3.450)
Insert Into @Temp Values(4.835)
Insert Into @Temp Values(2.764)
Insert Into @Temp Values(9.882)

Select Data
From   @Temp
Where  Data <> Floor(Data * 100) / 100

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

SELECT *
       FROM YourTable
WHERE CAST(YourField*1000 AS int) % 10 > 0

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Bluejay07]

Thank you both for your reply.

Forums like this are great since a different approach to a problem can be found from different people.

I have quickly tested both methods and they returned the same amount of rows (which is a great sign).

Thank you again for a solution.

A star to you both.

Have a great day.

 

[bborissov]

With one specification:
George's suggestion is optimizable (if you have index on that field )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

I don't think so. I use the data column in a formula on the right side of the inequality check. I was actually thinking that your query would be slightly faster. You'd probably have to try pretty hard to time the difference though.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom