SQL . Find difference ( Variance ) only on 7 decimal places-CodePudding

I have a table ( Name = TABLE1) as below . Column VARIANCE = R1_NRI - R2_NRI . For COLUMN STATUS to be updated to "Changed" only 7 decimal places to be considered . For example Well3 and Well5 even though the last decimal place is changed which is the 8th decimal status to be updated "No change". Should consider only 7 decimal places and no rounding even though the columns data format of R1_NRI and R2_NR1 is 8 decimal places.

How to achieve this in SQL .

Select Name, R1_NRI, R2_NRI
       case when R1_NRI - R2_NRI <> 0 THEN 'No change' Else 'Changed' as STATUS
from Table1

CodePudding user response：

If you are using MS SQL SERVER the trick is this

select cast(10000000 * 0.123456789 as int)

and this results in

So your query would be

Select Name,R1_NRI,R2_NRI
case 
when CAST(10000000 * R1_NRI as int) - CAST(100000000 * R2_NRI as int) <> 0
 THEN 'No change' Else 'Changed' as STATUS from Table1