CODEWITHSUNDEEP
sqlsql-serverrounding
Cornflake2068
Cornflake2068

Reputation: 533

SQL rounding to decimal places

I have a dataset which i need to do a calculation on price and round to decimal places. But the results aren't quite what is expected. The calculation is in the case statement

CREATE TABLE #Temp ( ID INT IDENTITY(1,1), Price DECIMAL(7,2) )

INSERT INTO #TEMP ( Price )
VALUES ( 119.99 )
        , ( 48.99 )

SELECT
    ID
    , Price
    , CASE WHEN Price > 10 THEN CONVERT( DECIMAL(7,2), Price * 1.08 - 0.05 ) END AS RRP
FROM #Temp

DROP TABLE #Temp

with the results

ID  Price   RRP
1   119.99  129.54
2   48.99   52.86

I need to get the 129.54 to 129.55 and the 52.86 to 52.85 within the same case statement if that is possible to match up with another data set

Upvotes: 1

Views: 227

Answers (2)

xQbert
xQbert

Reputation: 35323

Assuming you want to round to the nearest 5 cents or nearst 5 in 100ths decimal position. 

INSERT INTO #TEMP ( Price )
VALUES ( 119.99 )
        , ( 48.99 )

SELECT
    ID
    , Price
    , Price * 1.08 - 0.05
    , CASE WHEN Price > 10 THEN CONVERT( DECIMAL(7,2), round((Price * 1.08 - 0.05)*20,0)/20) END AS RRP
FROM #Temp

DEMO:http://rextester.com/QIM12944

Upvotes: 0

justiceorjustus
justiceorjustus

Reputation: 1965

Are you rounding to the nearest 5 cents because your country has gotten rid of the penny? This is the only logic that we can seem to follow with your expected results.

SELECT ID
    ,Price
    ,CASE 
        WHEN Price > 10
            THEN ROUND(CONVERT(DECIMAL(7, 2), Price * 1.08 - 0.05) * 20, 0) / 20
        END AS RRP
FROM #Temp

Upvotes: 1

Related Questions