Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2013
    Posts
    46

    Unanswered: How to find the rows with records containing more than two numbers after the decimal

    How do I find the rows that have more than two decimal numbers after the decimal point for example 2.787686
    I am trying :

    select amount, LEN(AMOUNT) - CHARINDEX('.', amount) as DecimalCount from GL_REPORT
    but is not working.
    please help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh my... You've opened up a can of worms that is SO nasty that I'm not quite sure where or how to help you with it! Which is your "right" answer (note that all of them are "right", I'm asking which one of these is "right" for you).
    Code:
    DECLARE
       @f       FLOAT         = 2
    ,  @i       INT           = 2
    ,  @m       MONEY         = 2
    ,  @n       NUMERIC(9, 1) = 2
    ,  @r       REAL          = 2
    ,  @x       INT           = 999
    
    SELECT @f / @x, @i / @x, @m / @x, @n / @x, @r / @x
    Once we get that ironed out, then maybe I can start to help you!

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

  3. #3
    Join Date
    Feb 2013
    Posts
    46
    My column is money and I have amounts showing with four digits after the decimal, the data got transferred this way and now I have to pull the amounts somehow and get rid of the extra two digits without using ROUND.

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    select MyColumn * 100 % 1
    -- ...
    Hope this helps.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by M1N View Post
    My column is money and I have amounts showing with four digits after the decimal, the data got transferred this way and now I have to pull the amounts somehow and get rid of the extra two digits without using ROUND.
    Why can't you use the Round() function?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without understanding precisely why you aren't allowed to use Round(), I can give you 200-300 answers. There is a very high probability that one or more of those answers will be correct, but it isn't certain.

    I'm going to guess that the column stored as MONEY really is money and that you've got totals which have two decimal place accuracy that you need to match. Even if you use syntax that doesn't include a call to Round() like Imex provided, you are still effectively rounding under the covers as well as adding a new source of inconsistency to your data.

    There's always a solution, but especially in the kind of problem that I've sketched out those answers aren't always obvious. There is no simple "one size fits all" solution for this family of problems... These need to be solved one at a time to meet the user requirements!

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

  7. #7
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Just trying to think outside the box a little. What about some sort of truncation?

    Code:
      SELECT 1.23456 AS number INTO #test
      SELECT left(number,charindex('.',number)+2) FROM #test
      drop table #test

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by clawlan View Post
    Just trying to think outside the box a little. What about some sort of truncation?
    Good thought, but truncation is really just another form of rounding (and usually produces the same results). This isn't acceptable for formal financial reporting.

    GAAP, JSOX, IFRS, etc. each have clear, well defined procedures to deal with this kind of issue. Whatever system or policy that M1N needs to work within almost certainly has one too. We just need to understand the requirements, then we can help with suggestions on how to implement the required policy.

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

  9. #9
    Join Date
    Feb 2013
    Posts
    46
    I need to pull all the records that have 4 digits after the decimal this are the requirements.

  10. #10
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    Quote Originally Posted by M1N View Post
    I need to pull all the records that have 4 digits after the decimal this are the requirements.
    something like this?

    Code:
    WHERE len(substring(cast(number AS varchar),charindex('.',number)+1,len(number)-charindex('.',number))) = 4

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by M1N View Post
    I need to pull all the records that have 4 digits after the decimal this are the requirements.
    The MONEY datatype always has four digits, it is defined that way. Just pull all of the rows and you'll be fine.

    This is exactly the point that I was trying to make from my first post. Without knowing exactly what M1N needed I was way off base assuming that something more complex was required.

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

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Manytimes,
    the most difficulty to answer an issue might be to define/understand the requirements exactly and undoubtly,
    for all possible data and in all situations(in assumed environment including the datatype).


    On this thread, you wrote first time
    Quote Originally Posted by M1N View Post
    How do I find the rows that have more than two decimal numbers after the decimal point for example 2.787686
    I am trying :

    select amount, LEN(AMOUNT) - CHARINDEX('.', amount) as DecimalCount from GL_REPORT
    but is not working.
    please help.
    That first requirement("find the rows that have more than two decimal numbers after the decimal point for example 2.787686")
    was alredy changed to
    I need to pull all the records that have 4 digits after the decimal this are the requirements.
    So, first my wants for you was that please clarify your requirements flawlessly, considering exceptional data.


    You might think "have 4 digits after the decimal" were enough to describe your requirements.
    But, without datatype(including number of digits and fractions),
    some data having 4 digits after the decimal might be possible, like
    '123.4567', '1234.0045', '123.4500', '123.0400', '12.3040', so on...

    Are you required all of these data?
    If so, the answer might be already PatP provided, like...
    The MONEY datatype always has four digits, it is defined that way. Just pull all of the rows and you'll be fine.
    Note: Ialic and Bold/Ialic were by me.

  13. #13
    Join Date
    Feb 2013
    Posts
    46
    Thanks fro the help, I didn't know that money datatype is defined as four digits.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Tonkuma:
    Quote Originally Posted by M1N View Post
    My column is money and I have amounts showing with four digits after the decimal, the data got transferred this way and now I have to pull the amounts somehow and get rid of the extra two digits without using ROUND.
    The MONEY datatype is a Sybase/Microsoft datatype which is stored as a 64 bit signed integer that is implicitly divided by 10000 which gives it four digits of scale.

    Since the requirement is four digits to the right of the decimal place, every value for the MONEY datatype implicitly meets N1M's requirement.

    Based on the original description, I thought that there were check totals with N digits of precision that needed to be matched so a simple Round() or truncate would not suffice. For those, detection is easy but correction (aka banker's rounding) is harder.

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

  15. #15
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Can you get rid of MONEY?

    I didn't know that money datatype is defined as four digits.
    Did you also not know it does not do correct math? 1) It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. We were still doing display formatting in the database in the 1970's and this was how you got $, commas and decimal signs in the output.

    2) Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.

    3) I violates many current GAPP and EU rules about precision

    4) The MONEY data type has rounding errors.

    Using more than one operation (multiplication or division) on money columns will produce severe rounding errors. A simple way to visualize money arithmetic is to place a ROUND() function calls after every operation. For example,

    Amount = (Portion / total_amt) * gross_amt

    can be rewritten using money arithmetic as:

    Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)

    Rounding to four decimal places might not seem an issue, until the numbers you are using are greater than 10,000.
    BEGIN
    DECLARE @gross_amt MONEY,
    @total_amt MONEY,
    @my_part MONEY,
    @money_result MONEY,
    @float_result FLOAT,
    @all_floats FLOAT;

    SET @gross_amt = 55294.72;
    SET @total_amt = 7328.75;
    SET @my_part = 1793.33;

    SET @money_result = (@my_part / @total_amt) * @gross_amt;
    SET @float_result = (@my_part / @total_amt) * @gross_amt;
    SET @Retult3 = (CAST(@my_part AS FLOAT)
    / CAST( @total_amt AS FLOAT))
    * CAST(FLOAT, @gross_amt AS FLOAT);

    SELECT @money_result, @float_result, @all_floats;
    END;

    @money_result = 13525.09 -- incorrect
    @float_result = 13525.0885 -- incorrect
    @all_floats = 13530.5038673171 -- correct, with a -5.42 error

Posting Permissions

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