Round Function

[cardi]

Hi,

I have a question for you. I need to round a number. I did this function... It works wells, but sometimes it gave me an error

DECLARE @Amount float(53)
--SET @Amount = 9249599.0 --'Problem.. why??
SET @Amount = 54249 -- Same thing
--SET @Amount = 892499.0 -- Again
--SET @Amount = 8892499.0 -- ok

DECLARE @Result float(53)

IF @Amount >= 10000000.0
SET @Result = ROUND(@Amount, -6)
ELSE
IF @Amount >= 1000000.0
SET @Result = ROUND(@Amount, -5)
ELSE
IF @Amount >= 100000.0
SET @Result = ROUND(@Amount, -3)
ELSE
IF @Amount >= 10000.0
SET @Result = ROUND(@Amount, -2)
ELSE
SET @Result = @Amount

select @Amount as sortie union select @Result -- just to see the result

---

Could you help me?? I don't know why??

Thanks

 

[bborissov]

What is the problem here?
ROUND(9249599,-6) is 9200000 because NEXT digit after the last meaning digit is 4. And the is how the ROUND() in ALL languages works, because this is a mathematical rule. 49 is smaller the 50 so the number MUST be 9200000 even if after the 92 you have 49999 ->9249999 it also should be 9200000.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[cardi]

The problem is if I take the first "SET" (SET @Amount = 9249599.0), my result is...

@Amount = 9200000.0
@Result = 9249599.0

It should be the opposite !!! I don't understand

 

[bborissov]

Are you sure?

DECLARE @Amount float(53)
SET @Amount = 9249599.0 --'Problem.. why??
--SET @Amount = 54249     -- Same thing
--SET @Amount = 892499.0  -- Again
--SET @Amount = 8892499.0 -- ok
    
DECLARE @Result float(53)

IF @Amount >= 10000000.0
    SET @Result = ROUND(@Amount, -6)
ELSE
IF @Amount >= 1000000.0
    SET @Result = ROUND(@Amount, -5)
ELSE
IF @Amount >= 100000.0
    SET @Result = ROUND(@Amount, -3)
ELSE
IF @Amount >= 10000.0
    SET @Result = ROUND(@Amount, -2)
ELSE
    SET @Result = @Amount

SELECT @Result Res, @Amount Amt


--SET @Amount = 9249599.0 --'Problem.. why??
SET @Amount = 54249     -- Same thing
--SET @Amount = 892499.0  -- Again
--SET @Amount = 8892499.0 -- ok
    
IF @Amount >= 10000000.0
    SET @Result = ROUND(@Amount, -6)
ELSE
IF @Amount >= 1000000.0
    SET @Result = ROUND(@Amount, -5)
ELSE
IF @Amount >= 100000.0
    SET @Result = ROUND(@Amount, -3)
ELSE
IF @Amount >= 10000.0
    SET @Result = ROUND(@Amount, -2)
ELSE
    SET @Result = @Amount

SELECT @Result Res, @Amount Amt

SET @Amount = 892499.0  -- Again
--SET @Amount = 8892499.0 -- ok
    

IF @Amount >= 10000000.0
    SET @Result = ROUND(@Amount, -6)
ELSE
IF @Amount >= 1000000.0
    SET @Result = ROUND(@Amount, -5)
ELSE
IF @Amount >= 100000.0
    SET @Result = ROUND(@Amount, -3)
ELSE
IF @Amount >= 10000.0
    SET @Result = ROUND(@Amount, -2)
ELSE
    SET @Result = @Amount

SELECT @Result Res, @Amount Amt

SET @Amount = 8892499.0 -- ok
    
IF @Amount >= 10000000.0
    SET @Result = ROUND(@Amount, -6)
ELSE
IF @Amount >= 1000000.0
    SET @Result = ROUND(@Amount, -5)
ELSE
IF @Amount >= 100000.0
    SET @Result = ROUND(@Amount, -3)
ELSE
IF @Amount >= 10000.0
    SET @Result = ROUND(@Amount, -2)
ELSE
    SET @Result = @Amount

SELECT @Result Res, @Amount Amt

In that test the Res Column is always ROUNDed one.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[cardi]

I'm sorry. You lose your time with that!! With my UNION, SQL Server sorts the two results, so when it sorts "down", it reverses my two "SELECT".

I'm sorry, but thanks to take the time to answer me, I appreciate

 

[bborissov]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Try

Round(@Amount, 1 - Floor(Log(@Amount) / Log(10))) -- put literal value for Log(10)

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]