mister.magoo (8/22/2016)
when i need to compare two floats, I tend to use this syntaxWHERE ABS(Float1 - Float2)<@Variance
So, for comparing to 2 d.p. I set @Variance = 0.01
to compare to 4 d.p. set @Variance = 0.0001
etc...
The above is an excelent advise,
mister magoo's solution works with a fixed scale.
If the scale of the two floats is not known or can vary a lot, a 'relative' compare would be better. See the script below with some examples.
declare @a float = 0.0000383733
declare @b-2 float = 0.0000383743
-- declare @a float = 3837330000 -- Example two (has the same relative difference)
-- declare @b-2 float = 3837430000
print 1.0+(@a-@b)/@b -- Visual fast representation to see the relative difference.
IF ABS((@a-@b)/@b) < 0.001 PRINT 'Less' ELSE PRINT 'More'
IF ABS((@a-@b)/@b) < 0.0001 PRINT 'Less' ELSE PRINT 'More'
IF ABS((@a-@b)/@b) < 0.00001 PRINT 'Less' ELSE PRINT 'More'
IF ABS((@a-@b)/@b) < 0.000001 PRINT 'Less' ELSE PRINT 'More'
Ben