Post reply

Weird behavior comparing two float columns in SQL 2012

  • February 9, 2015 at 10:43 am

    #294599

    I am validating an ETL and am experiencing some weird behavior when I compare the numbers in my data mart to the numbers in the source data. In the source data I have a column for UnitCost and a column for Quantity, and the result of multiplying those within an account is the TotalCost. UnitCost is a float. In my datamart table I have defined the same column as float as well. When I would sum up the totals on each side using the same selection criteria, SQL would tell me I was about $2000 off in total. So I exported the data from the source system to a csv and imported/loaded it into a work table which has the account and the total (float). (Had to do this because of server issues). I did the same thing with the data mart table (work table with account and total (float). When I join the two work tables, the values in the columns look exactly the same. However, I created a calculated column called Diff1 where I put the difference between the data mart value and the source table value. For some reason, SQL thinks there is a difference. The difference is extremely small ( a number with an E-14 at the end would be pretty darn small) but when I add up Diff1 I end up with the $2000 difference. I cannot for the life of me see a difference between the two columns. They look like they have exactly the same values. So something is going on with the way SQL is looking at them. Any help is MUCH appreciated.

  • February 9, 2015 at 10:46 am

    #1776453

    Can you tell us the full data types of all the columns involved please?

    Edit: have a look here. It is likely that you are experiencing rounding errors at a very small scale.

  • February 9, 2015 at 10:50 am

    #1776454

    check how your calculations are performed,and see if there could be integer division involved!

    in SQL, an integer divided by an integer is an integer, that ramification means truncation .

    SELECT 10 / 45 = 0, and not 0.222222 that you would expect. the sum of a bunch of integer divisions could explain the difference you are encountering.

    you have to make sure at least one of the tiems is a decimal/float.

    SELECT 10 / 45.0

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 9, 2015 at 11:09 am

    #1776460

    Floats are not precise values, so I wonder if that isn't part or all of the issue.

    Can you convert them to varchar(50) or something and do a string comparison to find the actual differences?

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy