Way to show items where more than 5 decimal places occur?

2075 views sql-server
-2

I am trying to filter out some query results to where it only shows items with 6 decimal places. I don't need it to round up or add 0's to the answer, just filter out anything that is 5 decimal places or below. My current query looks like this: (ex. if item is 199.54215 i dont want to see it but if it is 145.253146 i need it returned)

select 
TRA_CODPLANTA,
TRA_WO,
TRA_IMASTER,
tra_codtipotransaccion,
tra_Correlativo,
TRA_INGRESOFECHA,
abs(tra_cantidadparcial) as QTY
from mw_tra_transaccion
where FLOOR (Tra_cantidadparcial*100000) !=tra_cantidadparcial*100000
and substring(tra_imaster,1,2) not in ('CP','SG','PI','MR')
and TRA_CODPLANTA not in ('4Q' , '5C' , '5V' , '8H' , '7W' , 'BD', 'DP')
AND tra_INGRESOFECHA > @from_date
and abs(tra_cantidadparcial) > 0.00000 

Any assistance would be greatly appreciated!

answered question

You could convert to varchar, then do a SUBSTRING starting from the '.' and if the LEN is greater than 5, it is a number with at least 6 decimal places. OR you could take the LEN - the CHARINDEX of the period, which would probably be easier

That's what where FLOOR (Tra_cantidadparcial*100000) !=tra_cantidadparcial*100000 already accomplishes, inasmuch as it considers non-zero digits only, and not literally the number of decimals used in storage. If it's not working, take a closer look at your source data (it won't necessarily work correctly for FLOAT data, for starters).

1 Answer

12

Here is an example using a conversion to varchar and using the LEN - the CHARINDEX of the decimal point:

DECLARE @temp DECIMAL(20, 10) = 123.4565432135

IF LEN(CAST(@temp AS varchar(20))) - CHARINDEX(CAST(@temp AS varchar(20)), '.', 0) > 5
    SELECT 1
ELSE
    SELECT 0

posted this

Have an answer?

JD

Please login first before posting an answer.