CODEWITHSUNDEEP
sqlsql-server
peter
peter

Reputation: 13491

How to do SQL Server rounding digit by digit?

A consultant has described a type of rounding I need to use in a financial application.

The following value: 0.01488

needs to be rounded in steps like this. We round each digit at a time,

0.0148 -> 0.015
0.015 -> 0.02

Thus the result is 0.02 (2 cents).

But if we did normal rounding to 2 dp the value 0.0148 would round to 0.01.

What is the name of this rounding? And how can I do it with SQL Server?

Update:

My example above is an arbitrary example. The starting value might be 0.15436798, i.e. any number of decimal places. It is related to the result of a previous multiplication. In that case I would need to round more times.

Upvotes: 1

Views: 607

Answers (4)

ViKiNG
ViKiNG

Reputation: 1334

You can create a scalar function to do this job. like:

CREATE function [dbo].[fn_roundx]( @num FLOAT,@round INT) 
   RETURNS FLOAT
AS
BEGIN
    DECLARE @count INT=10;

    WHILE @count > = @round
       BEGIN
          SET @num = ROUND(@num,@count);
          SET @count=@count-1;
       END;
    RETURN @num 
END

And then use it as per your conveneince for any depth of rounding you want? For example

DECLARE @num FLOAT=0.014887;    
SELECT @num AS ActualNumber,dbo.fn_roundx(@num,2) AS RoundedNumber

enter image description here

or 

DECLARE @num FLOAT=0.014887;  
SELECT @num AS ActualNumber,dbo.fn_roundx(@num,3) AS RoundedNumber

enter image description here

Sounds good?

Upvotes: 1

ViKiNG
ViKiNG

Reputation: 1334

How about something like following?

DECLARE @num decimal(20,5)=0.014880;
DECLARE @count INT=5;
DECLARE @round INT=2;

SELECT @num value;

WHILE @count > = @round
BEGIN
   SET @num = ROUND(@num,@count);
   SET @count=@count-1;
END;

SELECT @num value;

with following output..

enter image description here

for any number up to any number of round you want?

Upvotes: 1

Chetan Kulkarni
Chetan Kulkarni

Reputation: 404

Try this,

select round(round(0.01500,3),2)
select round(round(0.01488,3),2)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Use ROUND function twice

First time pass length as 3 to round 0.01488 to 0.01500 

SELECT Round(0.01488, 3) -- 0.01500

On top of it pass length as 2 to round 0.01500 to 0.02000

SELECT Round(0.01500, 2) -- 0.02000

finally it should be something like this 

DECLARE @num NUMERIC(16, 5) = 0.01488

SELECT Round(Round(@num, 3), 2)

Upvotes: 0

Related Questions