[FIXED] How can I compare two values in SQL to a given decimal precision when they are of different data types?

Issue

I'm trying to show that there are no significant differences between two tables using an EXCEPT query. I'm only including the fields that I care about comparing. The problem is that tons of differences are being picked up in the result set due to one of the tables having data in a float format and the other having Decimal(24,7). I want my EXCEPT query to only include differences that are greater than 1.

I tried casting the float to Decimal(24,7) as well as casting both to Decimal(24,2) but due to rounding there are still differences flagged. For example, one table might show 2.55 and the other 2.5499999. That gets flagged as a difference. If I truncate the values, I still get differences (2.55 vs 2.54). If I round them or cast as Decimal(24,2) this particular instance is fixed, but others show up (e.g. rounding 2.355 vs 2.35499999 causes 2.36 vs 2.35).

How can I cast or round the decimal values such that any differences less than 1 are not returned by my EXCEPT query?

Sample code:

SELECT name, weight FROM Table1

EXCEPT

SELECT name, weight FROM Table2

/* That returns thousands of differences. If I cast both weights as Decimal(24,2) I get far fewer differences, but I want to only show differences greater than 1. */

Solution

This is probably not appropriate for EXCEPT. But you can try using a smaller number of decimal places:

SELECT name, CAST(weight as DECIMAL(24, 3)) FROM Table1
EXCEPT
SELECT name, CAST(weight as DECIMAL(24, 3)) FROM Table2;

Alternatively, you can use NOT EXISTS:

SELECT name, weight
FROM Table1 t1
WHERE NOT EXISTS (SELECT 1
                  FROM table2 t2
                  WHERE t2.name = t1.name AND
                        ABS(t2.weight - t1.weight) < 0.00001
                 );

Answered By - Gordon Linoff

Answer Checked By - Timothy Miller (PHPFixing Admin)