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. 😛 Boooo!