SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


6 places after decimal without rounding


6 places after decimal without rounding

Author
Message
PSB
PSB
SSC Eights!
SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)SSC Eights! (971 reputation)

Group: General Forum Members
Points: 971 Visits: 1536
I have a sample table

CREATE TABLE dbo.Analysis
(
ID INT IDENTITY,
Margin [numeric](21, 6) NULL,
Gallons INT,
Freight [numeric](21, 6) NULL,
AccMargin [numeric](21, 6) NULL)

INSERT INTO dbo.Analysis ( Margin,Gallons,Freight)
SELECT 0.050220,5022,-30.180000

INSERT INTO dbo.Analysis ( Margin,Gallons,Freight)
SELECT 0.050220,-5022,318.260000

UPDATE dbo.Analysis
SET AccMargin = (MArgin/Gallons + Freight/Gallons)

I want the AccMargin to be -0.005999 and -0.063383 without rounding . Currently it is rounding up to -0.006000
and -0.063383 for -0.005999 . I want 6 places after the decimal without rounding.

select *,(MArgin/Gallons + Freight/Gallons) As CorrectAccMargin from dbo.Analysis
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10242 Visits: 11959
See BOL for descrioption of ROUND function.

...
function
Is the type of operation to perform. function must be tinyint, smallint, or int. When function is omitted or has a value of 0 (default), numeric_expression is rounded. When a value other than 0 is specified, numeric_expression is truncated.

dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7217 Visits: 6431
You can try this:


CREATE TABLE #Analysis
(
ID INT IDENTITY,
Margin [numeric](21, 6) NULL,
Gallons INT,
Freight [numeric](21, 6) NULL,
AccMargin [numeric](21, 6) NULL)

INSERT INTO #Analysis ( Margin,Gallons,Freight)
SELECT 0.050220,5022,-30.180000

INSERT INTO #Analysis ( Margin,Gallons,Freight)
SELECT 0.050220,-5022,318.260000

UPDATE #Analysis
SET AccMargin = FLOOR(10000000*(CAST(MArgin AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7)) +
CAST(Freight AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7))))/10000000

SELECT * FROM #Analysis

DROP TABLE #Analysis




You might also want to try it without the CASTs but I think they'll be necessary.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85034 Visits: 41074
Considering that you're only working with 6 digits to begin with, consider transposing your formula for a smidgen more accuracy.

UPDATE dbo.Analysis
SET AccMargin = ((MArgin+Freight)/Gallons)




Of course, since you're doing decimal division, you should as consider expanding the precision and scale of the calculation by doing as Dwain suggested with CAST and then round to the correct number of decimal places.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10242 Visits: 11959
Jeff Moden (3/4/2013)
Considering that you're only working with 6 digits to begin with, consider transposing your formula for a smidgen more accuracy.

UPDATE dbo.Analysis
SET AccMargin = ((MArgin+Freight)/Gallons)




Of course, since you're doing decimal division, you should as consider expanding the precision and scale of the calculation by doing as Dwain suggested with CAST and then round to the correct number of decimal places.


Not sure such a change in the formula will add any accuracy.
SUM will keep the 6 digits precision, while division will use implicit conversion to FLOAT before and implicit conversion to decimal with higher procision after.
So, there is no need in an explicit expanding, it will be done behind the scene:

SELECT SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'basetype'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'precision'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin + Freight), 'scale'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'basetype'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'precision'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, Margin/Gallons), 'scale'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'basetype'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'precision'),
SQL_VARIANT_PROPERTY(CONVERT(sql_variant, (Margin/Gallons + Freight/Gallons)), 'scale')
FROM #Analysis AS A



Output:

SUM numeric 22 6
Division numeric 32 17
Sum of divs numeric 33 17


And, of course, adding extra 3 arithmetic operations to the calculation won't increase precision. :-)

There is need for Dwain's overcomplicated formula.
Simple ROUND will do perfectly good here.
Vedran Kesegic
Vedran Kesegic
Say Hey Kid
Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)Say Hey Kid (708 reputation)

Group: General Forum Members
Points: 708 Visits: 1266
ROUND function has optional third parameter which can specify truncation instead of rounding. This will give you the result you want:

UPDATE #Analysis
SET AccMargin = ROUND(Margin/Gallons + Freight/Gallons, 6, 1)



ID   Margin      Gallons      Freight      AccMargin
1 0.050220 5022 -30.180000 -0.005999
2 0.050220 -5022 318.260000 -0.063383


If you want to be absolutely sure about retaining precision, CAST/CONVERT every value participating in a formula into DECIMAL(25,13), and subresults also. DECIMAL(25,13) is so called "magical" type (as well as every decimal with p+s=38) because it retains decimal precision after multiplication, division, addition, substraction. That many CAST-s look ugly, but that guarantees you will retain true 13 digits after decimal point, before the final rounding/truncation.

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7217 Visits: 6431
The ghostly apparition of the third parameter to ROUND makes its spooky appearance. w00t

So here's various solutions except for Sergiy's showing they all work and timing results for 2 (my money was on 2 or 5).


CREATE TABLE #Analysis
(
ID INT IDENTITY,
Margin [numeric](21, 6) NULL,
Gallons INT,
Freight [numeric](21, 6) NULL,
AccMargin [numeric](21, 6) NULL)

INSERT INTO #Analysis ( Margin,Gallons,Freight)
SELECT 0.050220,5022,-30.180000

INSERT INTO #Analysis ( Margin,Gallons,Freight)
SELECT 0.050220,-5022,318.260000

SELECT ID, Margin, Gallons, Freight
,AccMargin1 = FLOOR(10000000*(CAST(MArgin AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7)) +
CAST(Freight AS DECIMAL(22,7))/CAST(Gallons AS DECIMAL(22,7))))/10000000
,AccMargin2 = FLOOR(10000000*((MArgin + Freight)/Gallons))/10000000
,AccMargin3 = FLOOR(10000000*(MArgin/Gallons + Freight/Gallons))/10000000
,AccMargin4 = ROUND(MArgin/Gallons + Freight/Gallons, 6, 1)
,AccMargin5 = ROUND((MArgin + Freight)/Gallons, 6, 1)
FROM #Analysis

;WITH Tally (n) AS (
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns a CROSS JOIN sys.all_columns b)
INSERT INTO #Analysis (Margin, Gallons, Freight)
SELECT RAND(CHECKSUM(NEWID())), CASE gallons WHEN 0 THEN 100 ELSE gallons END
,CHECKSUM(NEWID()) % 500
FROM Tally
CROSS APPLY (SELECT CHECKSUM(NEWID()) % 10000) a (gallons)

DECLARE @BlackHole [numeric](21, 6)

PRINT 'AccMargin2'
SET STATISTICS TIME ON
SELECT @BlackHole = FLOOR(10000000*((MArgin + Freight)/Gallons))/10000000
FROM #Analysis
SET STATISTICS TIME OFF

PRINT 'AccMargin5'
SET STATISTICS TIME ON
SELECT @BlackHole = ROUND((MArgin + Freight)/Gallons, 6, 1)
FROM #Analysis
SET STATISTICS TIME OFF

DROP TABLE #Analysis




And the results are in:


AccMargin2

SQL Server Execution Times:
CPU time = 1014 ms, elapsed time = 1010 ms.
AccMargin5

SQL Server Execution Times:
CPU time = 858 ms, elapsed time = 865 ms.



With ROUND being our ghoulishly delicious winner by a narrow ectoplasmic appendage. :-P Boooo!


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85034 Visits: 41074
Sergiy (3/4/2013)
Not sure such a change in the formula will add any accuracy.
SUM will keep the 6 digits precision, while division will use implicit conversion to FLOAT before and implicit conversion to decimal with higher procision after.
So, there is no need in an explicit expanding, it will be done behind the scene:


Wow! You know me... I had to try it and you're absolutely correct. I don't know why I thought SQL Server was less capable than that. I took a fringe case to prove what you say is correct. Life just became a whole lot easier. Thanks, Sergiy, and nice proof!

Here are the simple tests I did using a fringe case to prove what Sergiy said. I haven't tested to see if or when getting rid of the extra division operator provides a real return on performance but the math works out just like he said.

--===== Using the NUMERIC datatype
DECLARE @Margin NUMERIC (21,6),
@Gallons INT,
@Freight NUMERIC (21,6)
;
SELECT @Margin = 0.000001,
@Gallons = 2,
@Freight = 0.000001
;
SELECT @Margin/@Gallons + @Freight/@Gallons;
SELECT (@Margin + @Freight) / @Gallons;
SELECT @Margin/@Gallons, @Freight/@Gallons;
GO
--===== Using the DECIMAL datatype (just to make sure BOL was actually correct)
DECLARE @Margin DECIMAL (21,6),
@Gallons INT,
@Freight DECIMAL (21,6)
;
SELECT @Margin = 0.000001,
@Gallons = 2,
@Freight = 0.000001
;
SELECT @Margin/@Gallons + @Freight/@Gallons;
SELECT (@Margin + @Freight) / @Gallons;
SELECT @Margin/@Gallons, @Freight/@Gallons;



--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search