Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6

    Unanswered: Comparing Numbers

    So......

    It's me again.....

    If you are doing a quality check on numbers? For instance, if you have an extended sell field and you want to calc the fields unit price * quantity to see if the extended price report is correct.

    I have casted to float and then built a temp table to != against the calc field and the reported field but it still is pulling back matches.

    Maybe I should recreate my temp table to varchar the characters then compare using the #temp table?

    Thoughts?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Try using the Numeric datatype, rather than Float. Float tends to suffer from rounding errors.

  3. #3
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I ended up casting twice for both sets of numbers
    Cast(Cast(column a as INT)) as varchar)
    I also did this with the math problem unit price * qty, using the
    CAST(CAST([qty] * [Unit_Price] as INT)) AS VARCHAR))
    I turned this into a CTE and then just did a select (columns I wanted) from CTE where extended_sell <> extended_sell_calculated

    Now here is the kicker, most of the records are only 1 dollar off. Any ideas or an approach on how to choke out those 1 dollar deltas and account for everything else?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Being off by one dollar smells like a rounding error, but to be sure, you may want to take the data that go into that calculation, and step through what it should be (down to a decimal place or two). Then you can walk that bunch of data through your calculations to see when/where it goes wrong.

  5. #5
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    I eventually went another route.

    I wrote a case statement QTY + Unit Price + 1, + 2, +3, +4 and then the same amount with negative numbers. This took our the small delta's and the tolerance is set at 5 +/- anyway so that worked and then I placed no match in the else, formed a CTE and filtered by no match. It worked well, I got the discrepancies down to 37 and all are legit errors.

    Just wanted to share my approach.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> If you are doing a quality check on numbers? <<

    Well, that means your DDL is screwed up really bad. Columns are not anything like fields. Columns can have constraints which will prevent bad data and you will not have to perform a post-mortem.

    >> For instance, if you have an extended sell field [sic] and you want to calculate the fields [sic] (unit_price * sale_qty) to see if the extended price report is correct.*<<

    How could it not be? You should have a table with something like this:

    CREATE TABLE Sales
    ( ..
    unit_price DECIMAL (12,2) NOT NULL CHECK (unit_price >= 0.00),
    sale_qty INTEGER NOT NULL CHECK(sale_qty > 0)
    ..);

    Then you can add extended_price as a computed column if you wish.

    >> I have casted to FLOAT and then built a temp table to != [sic: SQL uses <>; you are still writing C] against the calculated field [sic] and the reported field [sic] but it still is pulling back matches.*<<

    Temp table are how COBOL programmers fake having scratch tapes in the 21-st century. Using FLOAT for commercial work is not only inaccurate, but illegal. Many of the GAAP and EU regulations specify the number of decimal places and the rounding rules to be used. You might have to use DECIMAL(s,2), DECIMAL(s,4) or DECIMAL(s,5) in computations; check with the accounting department.

    >> Maybe I should recreate my temp table to VARCHAR(n) the characters then compare using the #temp table? <<

    You are just making it worse now. COBOL keeps numeric values in strings; SQL does not.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    VLOOKUP:

    Don't let Joe Celko's "interesting" presentation throw you... In spite of his sub-optimal social skills, Joe really does know databases and programming better than almost everyone on the planet. Joe has published many great books on SQL (and other database engines), has lectured for years (yes, he's even older than I am), and has worked on more interesting assignments than anyone I've ever known.

    With that said, Joe presents himself online as someone who has spent a bit too much of his life in bars and classrooms. Once you get past the "grumpy olde phart" persona, Joe knows a lot and will help you immensely. It is sometimes rough getting past the initial shock, but it is worth the effort!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2014
    Posts
    268
    Provided Answers: 6
    Hey I am just trying my best!

    I appreciate Joe's info and yours. Good stuff!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •