significant figure round/convert
-
Hi,
How do I convert these numbers:
1248 to 1250 (3 significant figure)
1243 to 1240 (3 significant figure)
136.6 to 137 (3 significant figure)
12.46 to 14.5 (3 significant figure)
if the field already has 3 significant figures or less then do not convert/round
.024 to .024 (Already has less then 3 significant figures)
123 to 123 (Already 3 significant figures)
12.6 to 12.6 (Already 3 significant figures)
Thanks in advance!
Khanh
-
By using some "JBM PFM"... this is actually a programable converter in that you can program the number of significant digits just by changing the contents of a single variable... this would make a great function...
Most of the following code is just test setup code... the last "paragraph" of code is where the rubber meets the road...
--===== Create a variable to hold the base test number
DECLARE @TestDigits DECIMAL(38,19)
SET @TestDigits = 1.245600085
--===== Create a variable to hold the number of significant digits to round to
-- This is NOT test setup code and would need to be included in a function
DECLARE @SigDigits TINYINT
SET @SigDigits = 3 --LOOK! Change this to vary the number of significant digits
--===== If the test table exists, drop it
IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL
DROP TABLE #MyHead
--===== Create the test table
CREATE TABLE #MyHead
(
RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TestValue DECIMAL(38,19)
)
--===== Populate the test table with values based on the base test value
INSERT INTO #MyHead (TestValue)
SELECT @TestDigits*1000000000. UNION ALL
SELECT @TestDigits*100000000.0 UNION ALL
SELECT @TestDigits*10000000.00 UNION ALL
SELECT @TestDigits*1000000.000 UNION ALL
SELECT @TestDigits*100000.0000 UNION ALL
SELECT @TestDigits*10000.00000 UNION ALL
SELECT @TestDigits*1000.000000 UNION ALL
SELECT @TestDigits*100.0000000 UNION ALL
SELECT @TestDigits*10.00000000 UNION ALL
SELECT @TestDigits*1.000000000 UNION ALL
SELECT @TestDigits*.1000000000 UNION ALL
SELECT @TestDigits*.0100000000 UNION ALL
SELECT @TestDigits*.0010000000 UNION ALL
SELECT @TestDigits*.0001000000 UNION ALL
SELECT @TestDigits*.0000100000 UNION ALL
SELECT @TestDigits*.0000010000 UNION ALL
SELECT @TestDigits*.0000001000 UNION ALL
SELECT @TestDigits*.0000000100 UNION ALL
SELECT @TestDigits*.0000000010 UNION ALL
SELECT @TestDigits*.0000000001
--===== Display the values rounded to @SigDigits significant digits
-- This is NOT test setup code and would need to be included in a function
SELECT TestValue,
STR(ROUND(TestValue, @SigDigits-1-FLOOR(LOG10(TestValue))),
38,CAST(
CASE
WHEN @SigDigits-1-FLOOR(LOG10(TestValue)) < 0
THEN 0
ELSE @SigDigits-1-FLOOR(LOG10(TestValue))
END
AS INT)
) AS Rounded
FROM #MyHead
Don't change DECIMAL(38,19) to REAL or FLOAT because you will get some pretty unpredictable results including the occasional scientific notation return.
"Send Beer... I already have enough pretzels!"
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is an example of something you can use in a query:
Declare @N Dec(38,19)
set @N = 23.4500678
select @N,Round(@N/Power(10,FLOOR(LOG10(ABS(@N)))),3)*Power(10,FLOOR(LOG10(ABS(@N))))
Beware of using Float instead of Decimal as this has round off issues. Read up on Decimal to find the parameters that fit your data, a Decimal(38,19) takes 17 bytes to store but has lots of significant digits.
-cheers-
--Mike
-
Careful... won't get down into the decimal places...
TestValue
---------------------------------------- ----------------------------------------
1245600085.0000000000000000000 1246000000.00000000
124560008.5000000000000000000 124600000.00000000
12456000.8500000000000000000 12460000.00000000
1245600.0850000000000000000 1246000.00000000
124560.0085000000000000000 124600.00000000
12456.0008500000000000000 12460.00000000
1245.6000850000000000000 1246.00000000
124.5600085000000000000 124.60000000
12.4560008500000000000 12.46000000
1.2456000850000000000 1.24600000
(11 row(s) affected)
Server: Msg 8134, Level 16, State 1, Line 58
Divide by zero error encountered.
Also, you need to change the "3" to a "2" to get it to 3 "significant" digits...
Declare @N Dec(38,19)
set @N = 23.4500678
select @N,Round(@N/Power(10,FLOOR(LOG10(ABS(@N)))),2)*Power(10,FLOOR(LOG10(ABS(@N))))
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, sorry I should have been more careful. Power takes its precision from the first argument. (10.0 and 10.000 yield different results for small numbers)
this should cover all bases:
Declare @N Dec(38,19), @ten Dec(38,19)
set @N = .0234500678
set @ten = 10.0
select @N,Round(@N/Power(@ten,FLOOR(LOG10(ABS(@N)))),2)*Power(@ten,FLOOR(LOG10(ABS(@N))))
just declare @ten the same as the column being rounded.
-Mike
-
I got it.
Thank you all for your help.
Khanh
-
Khanh,
If you came up with a solution different than any of those posted, would you share it with us? Thanks...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Mike,
Thanks for the feedback.... using the data in my test table, the function you built runs out of gas on the small numbers no matter how many decimal places are included in the value of @Ten....
TestValue
---------------------------------------- ----------------------------------------
1245600085.0000000000000000000 1250000000.000000
124560008.5000000000000000000 125000000.000000
12456000.8500000000000000000 12500000.000000
1245600.0850000000000000000 1250000.000000
124560.0085000000000000000 125000.000000
12456.0008500000000000000 12500.000000
1245.6000850000000000000 1250.000000
124.5600085000000000000 125.000000
12.4560008500000000000 12.500000
1.2456000850000000000 1.250000
.1245600085000000000 .125000
.0124560008500000000 .012500
.0012456000850000000 .001250
.0001245600085000000 .000125
.0000124560008500000 .000013
.0000012456000850000 .000001
.0000001245600085000 .000000
.0000000124560008500 .000000
.0000000012456000850 .000000
.0000000001245600090 .000000
...that's not your fault... it's a limit of the POWER function... sure wish Microsoft would advertise these type of limitations...
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff, the problem is with the multiply not the Power function....very strange...
try this:
select cast(2.34 as dec(38,19)) * .000000001
you will get .000000002340000000
then try this:
select cast(2.34 as dec(38,19)) * cast(.000000001 as dec(38,19))
you get .000000
or
select cast(cast(2.34 as dec(38,19)) * cast(.000000001 as dec(38,19)) as dec(38,19))
you get .0000000000000000000
seems like a MS feature to me
-
OK, now I understand...from the books on-line:
The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.
Operation Result precision Result scale * e1 + e2 max(s1, s2) + max(p1-s1, p2-s2) + 1 max(s1, s2) e1 - e2 max(s1, s2) + max(p1-s1, p2-s2) max(s1, s2) e1 * e2 p1 + p2 + 1 s1 + s2 e1 / e2 p1 - s1 + s2 + max(6, s1 + p2 + 1) max(6, s1 + p2 + 1) * The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.
so my problem was the truncation when using dec(38,19) seemed strange, try this:
select cast(2.34 as dec(18,10)) * cast(.000000001 as dec(18,10))
gets: .00000000234000000000
with scale = 20
Thanks for having me chase this rabbit, I learned something that might bite me later.
-
I forgot about that bloody chart. Good find, Mike and thanks for the feedback.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi everyone,
Sorry for the long delay. I was on a short vacation. Here what I did and it seem to work for my report.
Replaced the decimal point with a '' then count the # of digits. IF the digits is greater than 3 then if the Finalresult is > than 1000 Round with -1 expression ELSE Round with 0 expression.
Param FinalResult FinalReportingResult
TDS 1806 1810
NO2-N 0.34 0.34
NO3-N 70.9 70.9
TDS 2502 2500
NO2-N 0.76 0.76
NO3-N 140.2 140
NO2-N 0.01 0.01
NO3-N 163.8 164
CASE WHEN LEN(REPLACE(FinalResult, '.', '')) > 3 THEN (CASE WHEN (FinalResult > 1000) THEN Round(FinalResult, - 1)
WHEN (FinalResult <= RDL) THEN FinalResult ELSE Round(FinalResult, 0) END) ELSE FinalResult END AS FinalReportResult
Thanks every one for your inputs.
Regards,
Khanh
-
Ok... what's "RDL"?
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Reporting Detection Limit or RDL is the term used in most Chemistry Laboratory routine analyses.
Thanks,
Khanh
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply