CODEWITHSUNDEEP
sql-servert-sql
DRastislav
DRastislav

Reputation: 1882

Custom rounding in SQL

How can I set rules for custom rounding in SQL?

I need set this rules:

if second number after decimal is 1-2   eg.  10,02 - round it to 0  result 10,00
if second number after decimal is 3-7   eg.  10,13 - round it to 5  result 10,15
if second number after decimal is 8-9   eg.  10,28 - round it to bigger number  result 10,3

Can someone help me how to do this? Round function works from 0-5 and above 5 but how to do "custom rounding function" based on this rules?

Thanks in advance.

Upvotes: 0

Views: 835

Answers (4)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can create your own function to use your custom rounding rules:

CREATE FUNCTION CustomRound(@num DECIMAL(18,2))
RETURNS DECIMAL(18,2)
AS
BEGIN
  RETURN ROUND(@num, 1, 1) + 
    CASE WHEN (@num - ROUND(@num, 1, 1)) * 100 BETWEEN 1 AND 2 THEN 0
         WHEN (@num - ROUND(@num, 1, 1)) * 100 BETWEEN 3 AND 7 THEN 0.05
         WHEN (@num - ROUND(@num, 1, 1)) * 100 BETWEEN 8 AND 9 THEN 0.1
    END
END

This function truncates the decimal number after first decimal place and add depending on the second decimal place a value to "round" as needed.

You can use the above function like this:

SELECT dbo.CustomRound(10.12) -- 10.10
SELECT dbo.CustomRound(10.02) -- 10.00
SELECT dbo.CustomRound(10.13) -- 10.15
SELECT dbo.CustomRound(10.28) -- 10.30

demo on dbfiddle.uk

You can also extend this function to be more dynamic using the custom rounding:

CREATE FUNCTION CustomRound(@num DECIMAL(18,6), @precision INT)
RETURNS DECIMAL(18,6)
AS
BEGIN
  DECLARE @prec INT = IIF(@precision > 0, @precision - 1, 0);
  
  RETURN ROUND(@num, @prec, 1) + 
    CASE WHEN (@num - ROUND(@num, @prec, 1)) * POWER(10, @precision) BETWEEN 1 AND 2 THEN 0.0 / POWER(10, @precision)
         WHEN (@num - ROUND(@num, @prec, 1)) * POWER(10, @precision) BETWEEN 3 AND 7 THEN 5.0 / POWER(10, @precision)
         WHEN (@num - ROUND(@num, @prec, 1)) * POWER(10, @precision) BETWEEN 8 AND 9 THEN 10.0 / POWER(10, @precision)
    END
END

You can use this function like this:

SELECT dbo.CustomRound(10.12, 2) -- 10.10
SELECT dbo.CustomRound(10.02, 2) -- 10.00
SELECT dbo.CustomRound(10.13, 2) -- 10.15
SELECT dbo.CustomRound(10.28, 2) -- 10.30

-- or    
SELECT dbo.CustomRound(10.102, 3) -- 10.100
SELECT dbo.CustomRound(10.002, 3) -- 10.000
SELECT dbo.CustomRound(10.103, 3) -- 10.105
SELECT dbo.CustomRound(10.208, 3) -- 10.210

demo on dbfiddle.uk

Upvotes: 1

Wouter
Wouter

Reputation: 2976

You can multiply by 20, then round to the whole number and divide by 20 again.

e.g.

SELECT ROUND(myNum * 20,0) / 20.0
FROM myTable;

Upvotes: 0

Shahbaz
Shahbaz

Reputation: 178

You can define your custome funtion and use it for ever

DECLARE @NUM REAL =10.01;
DECLARE @RESULT NVARCHAR(MAX)

IF @NUM=ROUND(@NUM,0)
BEGIN
    SET @RESULT=@NUM
END
ELSE
BEGIN
    DECLARE @NUM_STR VARCHAR(MAX)=CAST(@NUM AS VARCHAR(MAX)) -- CONVERT TO VARCHAR
    DECLARE @LEFT VARCHAR(MAX)= CASE WHEN @NUM>=0 THEN FLOOR(@NUM) ELSE CEILING(@NUM) END -- GET LEFT SIDE NUMBERS
    DECLARE @RIGHT VARCHAR(MAX)= REPLACE(@NUM_STR,CONCAT(@LEFT,'.'),'') -- GET RIGHT SIDE NUMBERS
    ... -- YOUR ROLES
END

Upvotes: 0

Zhorov
Zhorov

Reputation: 29943

One possible approach is the following statement:

SELECT
   [Number],
   CASE 
      WHEN ([Number] * 100) % 10 BETWEEN 3 AND 7 THEN ROUND([Number] + 0.02, 1) - 0.05
      ELSE ROUND([Number] + 0.02, 1)
   END [RoundedNumber]  
FROM (VALUES (10.00), (10.01), (10.12), (10.13), (10.14), (10.28), (10.29)) v ([Number])

Result:

Number  RoundedNumber
10.00   10.00
10.01   10.00
10.12   10.10
10.13   10.15
10.14   10.15
10.28   10.30
10.29   10.30

Upvotes: 1

Related Questions