Search code examples
sqlsql-server-2000

SQL Trouble working with long float numbers, math

I have 2 values in my inventory database. One is the QtyOnHand (float), and the other is a calculation of all the inventory transactions for that item (think SUM(TransQty) where transQty is also a float). Problem is, over time for unknown reasons some of these values fall out of sync. So now I'm trying to clean them up.

Basically, I'm taking QtyOnHand (which we are declaring as accurate), and subtracting the transaction total from that. Then making a correction entry into transactions for the difference. This works great until we get to numbers with lots of "decimal dust". Also, float seems to display 0 even in cases where the actual value is more like 0.0000000000000000237.

Here are some data that I'm having trouble correcting.

Example Data

The items with scientific notation are giving me trouble. Even the line showing Item PLXL-105-H, which shows -18, -18 is not displaying accurately, because my query is only returning values that are outside of 1% mismatch. I've been working entirely in TSQL, would it help to manipulate the values from C# program?

So, here's my question: how should I handle these numbers? I've tried converting them to DECIMAL(38,38), but that gives me arithmetic overflow. I need to get an accurate value representing the difference, then use that value to balance things out.

Solution

  • How are you trying to make the entry?

    Doing something like:

    with to_update as (
     select itemNumber, QtyOnHand, TransBal
     from t
)
update tb_table
    set offset = TransBal - QtyOnHand
    from to_update
    where to_udpate.itemNumber = tb_table.itemNumber

    should do the trick.

    I sounds like you are trying to take the data out of the database, manipulate it, and then put it back in. If you stay within the database, you won't have a problem, because the values never get converted to strings.

    By the way, for the decimal conversion, you should use decimal(38,19) or something. The scale parameter is for the number of digits to the right of the decimal point, so setting it to 38 only represents numbers between -1 and 1.