SQLServerCentral.com / SQL Server 2008 / T-SQL (SS2K8) / 6 places after decimal without rounding / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 31 Jul 2014 10:00:12 GMT20RE: 6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspx[quote][b]Sergiy (3/4/2013)[/b][hr]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:[/quote]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.[code="sql"]--===== Using the NUMERIC datatypeDECLARE @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;[/code]Tue, 05 Mar 2013 07:51:02 GMTJeff ModenRE: 6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspxThe 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).[code="sql"]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.180000INSERT INTO #Analysis ( Margin,Gallons,Freight)SELECT 0.050220,-5022,318.260000SELECT 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()) % 500FROM TallyCROSS APPLY (SELECT CHECKSUM(NEWID()) % 10000) a (gallons)DECLARE @BlackHole [numeric](21, 6)PRINT 'AccMargin2'SET STATISTICS TIME ONSELECT @BlackHole = FLOOR(10000000*((MArgin + Freight)/Gallons))/10000000FROM #AnalysisSET STATISTICS TIME OFFPRINT 'AccMargin5'SET STATISTICS TIME ONSELECT @BlackHole = ROUND((MArgin + Freight)/Gallons, 6, 1)FROM #AnalysisSET STATISTICS TIME OFFDROP TABLE #Analysis[/code]And the results are in:[code="plain"]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.[/code]With ROUND being our ghoulishly delicious winner by a narrow ectoplasmic appendage. :-P Boooo!Tue, 05 Mar 2013 05:47:14 GMTdwain.cRE: 6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspxROUND function has optional third parameter which can specify truncation instead of rounding. This will give you the result you want:[code="sql"]UPDATE #AnalysisSET AccMargin = ROUND(Margin/Gallons + Freight/Gallons, 6, 1)[/code][code="plain"]ID Margin Gallons Freight AccMargin1 0.050220 5022 -30.180000 -0.0059992 0.050220 -5022 318.260000 -0.063383[/code]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.Tue, 05 Mar 2013 03:05:01 GMTVedran KesegicRE: 6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspx[quote][b]Jeff Moden (3/4/2013)[/b][hr]Considering that you're only working with 6 digits to begin with, consider transposing your formula for a smidgen more accuracy.[code="sql"]UPDATE dbo.AnalysisSET AccMargin = ((MArgin+Freight)/Gallons) [/code]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.[/quote]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:[code="sql"]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[/code]Output:[code="plain"]SUM numeric 22 6Division numeric 32 17Sum of divs numeric 33 17[/code]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.Mon, 04 Mar 2013 22:08:29 GMTSergiyRE: 6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspxConsidering that you're only working with 6 digits to begin with, consider transposing your formula for a smidgen more accuracy.[code="sql"]UPDATE dbo.AnalysisSET AccMargin = ((MArgin+Freight)/Gallons) [/code]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.Mon, 04 Mar 2013 20:12:35 GMTJeff ModenRE: 6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspxYou can try this:[code="sql"]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.180000INSERT INTO #Analysis ( Margin,Gallons,Freight)SELECT 0.050220,-5022,318.260000UPDATE #AnalysisSET 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 #AnalysisDROP TABLE #Analysis[/code]You might also want to try it without the CASTs but I think they'll be necessary.Mon, 04 Mar 2013 18:32:10 GMTdwain.cRE: 6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspxSee BOL for descrioption of ROUND function.[quote]...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.[/quote]Mon, 04 Mar 2013 18:19:24 GMTSergiy6 places after decimal without roundinghttp://www.sqlservercentral.com/Forums/Topic1426545-392-1.aspxI have a sample tableCREATE 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.180000INSERT INTO dbo.Analysis ( Margin,Gallons,Freight)SELECT 0.050220,-5022,318.260000UPDATE dbo.AnalysisSET 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.006000and -0.063383 for -0.005999 . I want 6 places after the decimal without rounding.select *,(MArgin/Gallons + Freight/Gallons) As CorrectAccMargin from dbo.AnalysisMon, 04 Mar 2013 17:58:34 GMTPSB