Results 1 to 15 of 15

012214, 22:29 #1Registered User
 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.

012214, 23:05 #2Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Oh 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
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

012314, 01:41 #3Registered User
 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.

012314, 07:41 #4Registered User
 Join Date
 Apr 2012
 Posts
 213
Try something like this:
Code:select MyColumn * 100 % 1  ...

012314, 09:30 #5www.gvee.co.uk
 Join Date
 Jan 2007
 Location
 UK
 Posts
 11,444
Provided Answers: 12

012314, 10:13 #6Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Without understanding precisely why you aren't allowed to use Round(), I can give you 200300 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!
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

012314, 10:26 #7Registered User
 Join Date
 Oct 2010
 Location
 Atlanta, GA
 Posts
 211
Provided Answers: 2Just 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

012314, 11:10 #8Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Good 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.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

012314, 12:15 #9Registered User
 Join Date
 Feb 2013
 Posts
 46
I need to pull all the records that have 4 digits after the decimal this are the requirements.

012314, 12:30 #10Registered User
 Join Date
 Oct 2010
 Location
 Atlanta, GA
 Posts
 211
Provided Answers: 2

012314, 12:37 #11Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54The 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.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

012314, 14:03 #12Registered User
 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
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.
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.

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

012314, 15:34 #14Resident Curmudgeon
 Join Date
 Feb 2004
 Location
 In front of the computer
 Posts
 15,579
Provided Answers: 54Tonkuma: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.
PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.

012614, 11:27 #15Registered User
 Join Date
 Jan 2013
 Posts
 355
Provided Answers: 1Can you get rid of MONEY?
I didn't know that money datatype is defined as four digits.
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