|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, November 23, 2009 6:59 AM
Points: 42,
Visits: 46
|
|
All, I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.
I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.
I know there has to be a way but alas it has eluded me.
Please assist.
TIA,
Gabriel Boyer
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:40 AM
Points: 715,
Visits: 2,705
|
|
gboyer (8/31/2009) All, I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.
I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.
I know there has to be a way but alas it has eluded me.
Please assist.
TIA,
Gabriel Boyer
You could use the modulo operator, you first need to multiply your float by whichever number of decimals you want to check, cast as integer, and check the last digit with modulo 10.
Here's how:
DECLARE @f FLOAT
SET @f = 25.300
SELECT count(@f) WHERE cast((@f * 1000 /* that is for 2 decimals*/) AS INT)%10 = 0
SET @f = 25.301
SELECT count(@f) WHERE cast((@f * 1000) /* that is for 2 decimals*/ AS INT)%10 = 0
Tell me if that helps,
Cheers,
J-F
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 11,605,
Visits: 27,645
|
|
gboyer (8/31/2009) All, I am trying to find a count on all records in a table that have more the 2 numbers after the decimal point in an Amount Column. The field has a data type Float and when I run the len(function) I get the count with no more than 2 numbers after the decimal point.
I even tried the Charindex function to look for a 3 in this record (26.920000076293945) and the function returned a 0 telling me that a 3 was not found.
I know there has to be a way but alas it has eluded me.
Please assist.
TIA,
Gabriel Boyer
if you convert a float to a string, there is always going to be more than three chars after the decimal point. check out the results of my example table, you get values like 151.000000000000000000000 for the conversion when it rounds nice an pretty....so it wouldn't help to convert to a string.
i think the thing to do is to forget casting to string,a nd jsut find items that are not equal to the round to two decimal palces of the number....that would tell you that 26.92 <> 26.920000076293945 but that 151.00 = 151.000000000000000000000
create table #Example(exampleId int identity(1,1), ExampleTotal float) insert into #Example select 150.0 + (1.0 / RW) from ( select top 30 row_number() over (order by id) as RW,id from sysobjects ) x --results /* 151.000000000000000000000 150.500000000000000000000 150.333333333333333333333 150.250000000000000000000 150.200000000000000000000 150.166666666666666666666 150.142857142857142857142 150.125000000000000000000 150.111111111111111111111 */
select * from #Example where round(ExampleTotal,2) <> ExampleTotal --two decimal places
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, November 23, 2009 6:59 AM
Points: 42,
Visits: 46
|
|
Here is an example of my results with my T-Sql:
SELECT CHARINDEX('4', Amount, 1) AS Length, Amount, EnrollmentYear, CHARINDEX('4', SSN, 1) AS Expr1, CAST(Amount AS varchar(50)) AS CastExample FROM dbo.[Benefits.Benefits]
5 25.34 2009 0 25.34 0 26.92 2009 1 26.92 0 0 2009 1 0 0 0 2009 0 0 0 0 2009 0 0 0 0.9 2009 3 0.9 3 9.43 2009 6 9.43 0 26.92 2009 0 26.92 0 0.9 2009 6 0.9 0 0 2009 1 0 0 180.37 2009 6 180.37 0 0.9 2009 0 0.9 0 9.5799999237060547 2009 8 9.58 0 22.52 2009 6 22.52 0 0 2009 0 0 0 126.05999755859375 2009 0 126.06 0 0 2009 0 0 0 10.909999847412109 2009 8 10.91 0 0 2009 6 0 0 26.920000076293945 2009 8 26.92 0 2.809999942779541 2009 8 2.81 0 0 2009 0 0 0 5.61 2009 0 5.61 0 7.73 2009 1 7.73 0 26.92 2009 0 26.92 0 2.7000000476837158 2009 8 2.7 0 0 2009 3 0 3 3.42 2009 3 3.42 0 25 2009 3 25 0 0 2009 3 0 0 11.770000457763672 2009 8 11.77 0 126.06 2009 3 126.06 0 16.89 2009 3 16.89 0 2.81 2009 0 2.81 0 0 2009 0 0 0 19.5 2009 1 19.5 0 17.770000457763672 2009 0 17.77 0 80 2009 6 80 0 6.57 2009 0 6.57 0 0 2009 1 0 0 85 2009 0 85 3 9.4300003051757812 2009 0 9.43 0 0 2009 0 0 0 0 2009 3 0 0 0 2009 8 0 0 16.89 2009 0 16.89 0 0.9 2009 8 0.9 0 13.58 2009 3 13.58 0 0 2009 1 0 0 22.520000457763672 2009 0 22.52 0 0 2009 8 0 0 0 2009 8 0 2 24.55 2009 0 24.55 0 0 2009 6 0 0 0 2009 3 0 1 40 2009 0 40 0 0 2009 0 0 0 0 2009 8 0 NULL NULL NULL NULL NULL
Sorry for the mess.
I tried to do a cast to varchar and it rounded the result
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:40 AM
Points: 715,
Visits: 2,705
|
|
Have you even read the solutions we posted to your problem, before posting that data?
Both solutions will solve the problem you have actually with the float values. If you have problems understanding the way it works, please ask questions.
Cheers,
J-F
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, November 23, 2009 6:59 AM
Points: 42,
Visits: 46
|
|
| You're right. I am sorry. I have a tough admitting I do not understand something. With your solution, J-F Bergeron, I have no idea how to adapt it to my situation. And with not understanding I have no idea even where to begin to ask questions.
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:40 AM
Points: 715,
Visits: 2,705
|
|
Well, if that is the case, I will explain to you how you could implement it.
You said you wanted to count the number of occurences that had more than 2 decimals.
Here's how:
First Method : The modulo method.
SELECT count(* ) FROM YourTableName WHERE cast((FloatColumnName * 1000) /* that is for 2 decimals*/ AS INT)%10 = 0
Second Method, compare the rounding of the value with the value itself, to see if there was any more decimals
SELECT count(* ) FROM YourTableName WHERE round(FloatColumnName,2) <> FloatColumnName
Just put in your table name, and your column name, and it will give you the count you were expecting.
If you need more assistance, please feel free to post your question.
Cheers,
J-F
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Monday, November 23, 2009 6:59 AM
Points: 42,
Visits: 46
|
|
J-F, Thanks for the push. I went and looked again at the post from Lowell and with you help was able to move forward. Thanks again. Sorry lack of confidence is showing.
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:40 AM
Points: 715,
Visits: 2,705
|
|
Happy it helped,
and don't worry, we all have to start somewhere, just ask questions if you are not sure. But playing with the supplied code is always a good way to learn.
Have a nice day,
Cheers,
J-F
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Yesterday @ 8:21 PM
Points: 32,893,
Visits: 26,765
|
|
gboyer,
Since you're brand new to the forum, you might want to take a look at the article at the following URL...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you follow those steps (yep... it takes just a bit of time extra, but not much), I guarantee that you'll get better answers quicker for your future posts even if you're not 100% sure which question to ask.
And, welcome aboard...
--Jeff Moden "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|