0

So I have two identical values that are results of sum functions with the exact same length (no rounding is being done). (Update, data type is float)

Value_1 = 29.9539194336501 Value_2 = 29.9539194336501

The issue I'm having is when I do an IF statement for Value_1 = Value_2, it comes up as FALSE.

Value_1:

SELECT SUM([INVN_DOL])/SUM([AVG_DLY_SLS_LST_35_DYS]) end as DSO
FROM TABLE A

Value_2:

SELECT SUM ([Total_Inventory_Val]) / SUM ([Daily_Independent_Demand])
FROM TABLE B

Any idea why they may not be exactly equal and what I can do to get a TRUE value since they do match?

Thanks in advance

Brian Kubricky
  • 63
  • 1
  • 1
  • 9
  • 1
    Are you sure they are actually of same type and are equal? What happens when you cast them to `varbinary(8)`? (I assume you understand [how it works in principle](https://stackoverflow.com/q/588004/11683) and don't want to use [an epsilon](https://stackoverflow.com/questions/588004/is-floating-point-math-broken#comment3831071_588014)). – GSerg Jun 12 '17 at 21:03
  • 1
    Tip: To add to the comment by GSerg, you can use [`SQL_Variant_Property()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/sql-variant-property-transact-sql) to display the data type and related parameters, e.g. precision and scale, of an expression. For example: `select SQL_Variant_Property( ( SELECT SUM ([Total_Inventory_Val]) / SUM ([Daily_Independent_Demand]) FROM TABLE B ), 'BaseType' );`. – HABO Jun 13 '17 at 03:40

2 Answers2

2

The issue you are having here is that your are using a calculated value that is held within a float, which will by design be slightly imprecise at higher levels of precision, which is why you are getting your mismatch.

Use data types like decimal with a defined precision and scale to hold your values and calculation results and you should get consistent results.

iamdave
  • 12,023
  • 3
  • 24
  • 53
0
  • You can make use ROUND to limit the decimal points Or
  • Try the ABS and see if that works out.
DataWrangler
  • 1,804
  • 17
  • 32