SQL Server : how to map decimals to corrected values

Question

I have a situation where I get trip data from another company. The other company measures fuel with a precision of ⅛ gallon.

I get data from the other company and store it in my SQL Server table. The aggregated fuel amounts aren't right. I discovered that while the other company stores fuel in 1/8 gallons, it was sending me only one decimal place.

Furthermore, thanks to this post, I've determined that the company isn't rounding the values to the nearest tenth but is instead truncating them.

Query:

/** Fuel Fractions **/
SELECT DISTINCT ([TotalFuelUsed] % 1) AS [TotalFuelUsedDecimals]
FROM [Raw]
ORDER BY [TotalFuelUsedDecimals]

Results:

TotalFuelUsedDecimals
0.00
0.10
0.20
0.30
0.50
0.60
0.70
0.80

What I'd like is an efficient way to add a corrected fuel column to my views which would map as follows:

0.00 → 0.000
0.10 → 0.125
0.20 → 0.250
0.30 → 0.375
0.50 → 0.500
0.60 → 0.625
0.70 → 0.750
0.80 → 0.875
1.80 → 1.875

and so on

I'm new to SQL so please be kind.

Server is running Microsoft SQL Server 2008. But if you know a way better function only supported by newer SQL Server, please post it too because we may upgrade someday soon and it may help others.

Also, if it makes any difference, there are several different fuel columns in the table that I'll be correcting.

score 0 · Answer 1 · answered Nov 08 '16 at 17:34

While writing up the question, I tried the following method using a temp table and multiple joins which seemed to work. I expect there are better solutions out there to be had.

CREATE TABLE #TempMap
    ([from] decimal(18,2), [to] decimal(18,3))
;

INSERT INTO #TempMap
    ([from], [to])
VALUES
    (0.0, 0.000),
    (0.1, 0.125),
    (0.2, 0.250),
    (0.3, 0.375),
    (0.5, 0.500),
    (0.6, 0.625),
    (0.7, 0.750),
    (0.8, 0.875)
;

SELECT [TotalFuelUsed]
  ,[TotalFuelCorrect].[to] + ROUND([TotalFuelUsed], 0, 1) AS [TotalFuelUsedCorrected]
  ,[IdleFuelUsed]
  ,[IdleFuelCorrect].[to] + ROUND([IdleFuelUsed], 0, 1) AS [IdleFuelUsedCorrected]
FROM [Raw]
JOIN [#TempMap] AS [TotalFuelCorrect] ON [TotalFuelUsed] % 1 = [TotalFuelCorrect].[from]
JOIN [#TempMap] AS [IdleFuelCorrect] ON [IdleFuelUsed] % 1 = [IdleFuelCorrect].[from]
ORDER BY [TotalFuelUsed] DESC

DROP TABLE #TempMap;

score 0 · Answer 2 · edited Nov 09 '16 at 21:55

Try adding a column as:

select ....
, case when right(cast([TotalFuelUsed] as decimal(12,1)), 1) = 1 then [TotalFuelUsed] + 0.025 
            when right(cast([TotalFuelUsed] as decimal(12,1)), 1) = 2 then [TotalFuelUsed] + 0.05
            when right(cast([TotalFuelUsed] as decimal(12,1)), 1) = 3 then [TotalFuelUsed] + 0.075
            when right(cast([TotalFuelUsed] as decimal(12,1)), 1) = 6 then [TotalFuelUsed] + 0.025 
            when right(cast([TotalFuelUsed] as decimal(12,1)), 1) = 7 then [TotalFuelUsed] + 0.05
            when right(cast([TotalFuelUsed] as decimal(12,1)), 1) = 8 then [TotalFuelUsed] + 0.075
            else [TotalFuelUsed] end as updatedTotalFuelUsed

SQL Server : how to map decimals to corrected values

2 Answers2