Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
I work on sql server 2019 i face issue can't solve it

i need to display chemicalid that have different on chemicalmass and chemicalmassmodified

but if there are difference 0.01 then not display it as different i will consider it equal

so if difference between chemical mass and chemical mass modified is 0.01 i will consider it as equal and not difference

if difference between chemical mass and chemical mass modified is more than 0.01 then i will consider it difference

create table #mass
 (
 chemicalid  int,
 chemicalmass float,
 chemicalmassmodified float
 )
    
 insert into #mass(chemicalid,chemicalmass,chemicalmassmodified)
 values
 (12378,12,12),
 (67901,13,14),
 (33022,71,71.5),
 (55021,30.01,30.02),
 (47211,1.5,1.52),
 (90341,72,72),
 (20980,0.05,0.04),
 (87001,1.23,1.22)

Expected Result as below

chemicalid	chemicalmass	chemicalmassmodified
67901	13	14
33022	71	71.5
47211	1.5	1.52


What I have tried:

select * from #mass where 
chemicalmass <> chemicalmassmodified
Posted
Updated 28-Jun-22 0:45am
Comments
Richard MacCutchan 28-Jun-22 6:43am    
You need to calculate the difference between the two values and compare that to 0.01. Which, as far as I can see, should give you:
55021  30.01  30.02
90341  72     72
20980   0.05   0.04
87001   1.23   1.22

1 solution

Substract one from the other and compare the absolute difference to 0.01

See SQL Server ABS() Function[^]

You may get anomalies from using float - see SQL: Newbie Mistake #1: Using float instead of decimal - The Bit Bucket[^]
 
Share this answer
 
Comments
ahmed_sa 28-Jun-22 7:09am    
so how to solve issue and get expected result please
i try as below
SELECT *
FROM #mass
WHERE ABS(chemicalmass - chemicalmassmodified) > 0.01
but not working
CHill60 28-Jun-22 7:16am    
What does "not working" mean?
You are getting incorrect values because float only stores approximate values - read the link I provided. For example : for part 20980 the difference is 0.010000000000000001942890293094 - which is greater than 0.01

To get more accurate results use decimal for the column types instead. Then your code will work.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900