March 22, 2012 at 10:27 am
Hey all,
I have a percentage stored as a decimal as per this:
87.41 is 87.41%
It is stored in a decimal (10,7).
Now the majority are whole numbers. Some have two decimal places. What i am trying to do is find out what the minimum Dp`s i require in the database (i wouldnt be surprised if it were 2 - so a 5,2 would do me).
I cant think how to code this.
Any help greatfully received.
Dan
March 22, 2012 at 12:14 pm
would something like this work for you. not what parsename is intended for but I think it does the trick.
declare @dec decimal(10,7)
set @dec = 87.01
select len(parsename(cast(@dec as float),1) )
March 22, 2012 at 12:21 pm
danielfountain (3/22/2012)
Hey all,I have a percentage stored as a decimal as per this:
87.41 is 87.41%
It is stored in a decimal (10,7).
Now the majority are whole numbers. Some have two decimal places. What i am trying to do is find out what the minimum Dp`s i require in the database (i wouldnt be surprised if it were 2 - so a 5,2 would do me).
I cant think how to code this.
Any help greatfully received.
Dan
May I ask why you want to modify the decimal format? Is it possible that this was selected as it may be possible that they may need more the 2 places to the right of the decimal point? Just trying to play the devils advocate here.
March 22, 2012 at 12:22 pm
how about something like this to figure out the most decimal places used in the table:
SELECT MAX( CHARINDEX('.',REVERSE(CAST(CAST(val AS float) AS varchar(11)))) -1 )
FROM tablename
WHERE CHARINDEX('.',REVERSE(CAST(CAST(val AS float) AS varchar(11)))) > 0
March 22, 2012 at 12:32 pm
How about this?
DECLARE @tab TABLE ( Num DECIMAL(10,7)) ;
INSERT @tab (Num)
SELECT 87.41
UNION ALL SELECT 1.2
UNION ALL SELECT 1.0
UNION ALL SELECT 11
UNION ALL SELECT 1.22200
UNION ALL SELECT 1.2980
UNION ALL SELECT 1.2000000
UNION ALL SELECT 1.10009
UNION ALL SELECT 1.6709090
;
;WITH CTE AS
(
SELECT T.Num
,DecNum = CASE
WHEN CEILING(T.Num) = FLOOR(T.Num) THEN 0
WHEN CEILING(CrsApp1.Dec1) = FLOOR(CrsApp1.Dec1) THEN 1
WHEN CEILING(CrsApp2.Dec2) = FLOOR(CrsApp2.Dec2) THEN 2
WHEN CEILING(CrsApp3.Dec3) = FLOOR(CrsApp3.Dec3) THEN 3
WHEN CEILING(CrsApp4.Dec4) = FLOOR(CrsApp4.Dec4) THEN 4
WHEN CEILING(CrsApp5.Dec5) = FLOOR(CrsApp5.Dec5) THEN 5
WHEN CEILING(CrsApp6.Dec6) = FLOOR(CrsApp6.Dec6) THEN 6
WHEN CEILING(CrsApp7.Dec7) = FLOOR(CrsApp7.Dec7) THEN 7
END
FROM @tab T
CROSS APPLY ( SELECT T.Num * 10 ) CrsApp1 (Dec1)
CROSS APPLY ( SELECT T.Num * 100 ) CrsApp2 (Dec2)
CROSS APPLY ( SELECT T.Num * 1000 ) CrsApp3 (Dec3)
CROSS APPLY ( SELECT T.Num * 10000 ) CrsApp4 (Dec4)
CROSS APPLY ( SELECT T.Num * 100000 ) CrsApp5 (Dec5)
CROSS APPLY ( SELECT T.Num * 1000000 ) CrsApp6 (Dec6)
CROSS APPLY ( SELECT T.Num * 10000000 ) CrsApp7 (Dec7)
)
SELECT MaxDigitsAfterDecimal = MAX(C.DecNum)
FROM CTE C ;
March 22, 2012 at 12:44 pm
Robert klimes (3/22/2012)
would something like this work for you. not what parsename is intended for but I think it does the trick.
declare @dec decimal(10,7)set @dec = 87.01
select len(parsename(cast(@dec as float),1) )
This might produce wrong results because PARSENAME rounds certain values; try this sample data (rows with comments are rounded)
DECLARE @tab TABLE ( Num DECIMAL(10,7)) ;
INSERT @tab (Num)
SELECT 87.41
UNION ALL SELECT 1.2
UNION ALL SELECT 1.0
UNION ALL SELECT 11
UNION ALL SELECT 1.22200
UNION ALL SELECT 1.2980
UNION ALL SELECT 1.2000000
UNION ALL SELECT 1.10009
UNION ALL SELECT 1.6709090 -- PARSENAME Rounds this
UNION ALL SELECT 1.6709999 -- PARSENAME Rounds this
UNION ALL SELECT 123.009
;
and i dont knwo why it does that! 😀
March 22, 2012 at 1:18 pm
Ouch :pinch: Implicit conversions are causing the rounding
Look at this:
declare @dec decimal(10,7)
set @dec = 1.6709999
select flt = CAST ( @dec as float)
, Nvar = CAST ( @dec as nvarchar(23))
, synam = CAST ( @dec as sysname)
, fltnvar = CAST ( CAST ( @dec as float) as nvarchar(23))
, fltsynm = CAST ( CAST ( @dec as float) as sysname)
<DefinedValue>
<ColumnReference Column="Expr1005" />
<ScalarOperator ScalarString="parsename(CONVERT_IMPLICIT(nvarchar(23),CONVERT(float(53),[Num],0),0),(1))">
<Intrinsic FunctionName="parsename">
<ScalarOperator>
<Convert DataType="nvarchar" Length="46" Style="0" Implicit="true">
<ScalarOperator>
<Convert DataType="float" Scale="0" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Num" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
March 22, 2012 at 1:53 pm
Chris Harshman (3/22/2012)
how about something like this to figure out the most decimal places used in the table:
SELECT MAX( CHARINDEX('.',REVERSE(CAST(CAST(val AS float) AS varchar(11)))) -1 )
FROM tablename
WHERE CHARINDEX('.',REVERSE(CAST(CAST(val AS float) AS varchar(11)))) > 0
Nope! Again, incorrect results due to data type conversion and rounding!
March 22, 2012 at 2:00 pm
March 22, 2012 at 2:28 pm
actually I think it's the explicit conversion to FLOAT that messes up both of our solutions not any implicit conversion, so ColdCoffee's solution may be brute force, but without an easy way to determine the number of decimal places in a given value it may be the best solution for this problem.
March 22, 2012 at 2:49 pm
Chris Harshman (3/22/2012)
actually I think it's the explicit conversion to FLOAT that messes up both of our solutions not any implicit conversion
Acually explicit conversion to FLOAT preserves all the decimal values, but the cast to nvarchat after casting to float or using PARSENAME right over casting of float induces the rounding off. I/Us need to analyze further why this happens.
March 22, 2012 at 2:56 pm
ColdCoffee (3/22/2012)...but the cast to nvarchat after casting to float or using PARSENAME right over casting of float induces the rounding off...
seems to be a common problem, converting anything from float defaults to only 6 precision. if you use CONVERT function with style 128 it seems to work, but MSDN says 128 setting is depreciated :crazy:
SELECT MAX( CHARINDEX('.',REVERSE(CONVERT(varchar(20),CAST(val AS float),128))) -1 )
FROM tablename
WHERE CHARINDEX('.',REVERSE(CONVERT(varchar(20),CAST(val AS float),128))) > 0
I suppose it would be easier if you could control the trailing 0's on DECIMAL datatype conversion to VARCHAR instead of having to use FLOAT, but doesn't seem to be a way.
March 22, 2012 at 3:17 pm
Chris Harshman (3/22/2012)
if you use CONVERT function with style 128 it seems to work, but MSDN says 128 setting is depreciated :crazy:
:crazy:
Chris Harshman (3/22/2012)
I suppose it would be easier if you could control the trailing 0's on DECIMAL datatype conversion to VARCHAR instead of having to use FLOAT, but doesn't seem to be a way.
xactly. If we had the ability to control the trailing zeros , CAST and LEN would have easily gulped this problem. No funky logic required.
March 22, 2012 at 3:19 pm
Still haven't heard from the OP why he needs this.
March 23, 2012 at 2:07 am
Lynn Pettis (3/22/2012)
Still haven't heard from the OP why he needs this.
Hi,
The reason i need this is not that i am actually going to change the format - but i want to display the results in reporting services with the least amount of decimal places but without data loss. Having the majority look like 60.0000000 looks silly. However some have 60.5 or 60.55 - what i dont know is to what DP it is used. I dont want to round and lose any data.
Hope this helps.
Dan
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply