15,669,438 members
See more:
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
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
```

Solution 1

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[^]

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.