CODEWITHSUNDEEP
sql-serverrounding
kris
kris

Reputation: 39

Rounding the value to the nearest 50

I am trying to round down the value to the nearest 50. 1-50 it should round down to below 00 and when its 51-rest then it should round down to 50

ex:

I tried this,its wrking good but I need smething other than case statement

@ResultAmount = ROUND(@ResultAmount, -2, 1) + 
    CASE    WHEN RIGHT(CONVERT(INT, FLOOR(@ResultAmount)), 2) IN (00, 50)
            THEN RIGHT(CONVERT(INT, FLOOR(@ResultAmount)), 2)
            WHEN RIGHT(CONVERT(INT, FLOOR(@ResultAmount)), 2) BETWEEN 1 AND 49
            THEN 00
            WHEN RIGHT(CONVERT(INT, FLOOR(@ResultAmount)), 2) BETWEEN 51 AND 99
            THEN 50
            END

Thanks in advance!!!

Upvotes: 2

Views: 12547

Answers (4)

steryd
steryd

Reputation: 481

You can calculate modulo 50 and use this to reduce the original value

DECLARE @ResultAmount int = 243

SELECT @ResultAmount - (@ResultAmount%50)

Upvotes: 0

Cato
Cato

Reputation: 3701

This is all you need

SELECT FLOOR(@ResultAmount / 50) * 50;

e.g below

declare @ResultAmount decimal(10,2) = 249;

SELECT FLOOR(@ResultAmount / 50) * 50;

SET @ResultAmount = 250;

SELECT FLOOR(@ResultAmount / 50) * 50;

SET @ResultAmount = 200;

SELECT FLOOR(@ResultAmount / 50) * 50;

SET @ResultAmount = 199;

SELECT FLOOR(@ResultAmount / 50) * 50;

Upvotes: 7

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131581

You can simply divide the number by 50, round then multiply by 50 again, eg:

select cast(round(@i/50.0,0)*50 as int)

This will return 500 if @i is 524 but 550 if @i is 525.

You can create a function to make this easier: 

create function fn_Round_By(@input int,@divider float) 
returns int
as
begin 
    RETURN (cast(round(@input/@divider,0)*@divider as int));
end

Again, select dbo.fn_Round_By(525,50) returns 550 andselect dbo.fn_Round_By(524,50)` returns 500.

If you want values less than 50 to round up to 50, you can use a simple CASE, eg:

create function fn_Round_By(@input int,@divider float) 
returns int
as
begin 
    RETURN (
        CASE 
            WHEN @input <=@divider then @divider
            else cast(round(@input/@divider,0)*@divider as int)
        END
        );
end

Rounding down is performed by the FLOOR function so a function that rounded down to a specific interval would be: 

create function fn_Floor_By(@input int,@divider float) 
returns int
as
begin 
    RETURN (cast(FLOOR(@input/@divider)*@divider as int));
end

or, preserving the logic that rounds up anything under 50:

create function fn_Floor_By(@input int,@divider float) 
returns int
as
begin 
    RETURN (
        CASE 
            WHEN @input <=@divider then @divider
            else cast(FLOOR(@input/@divider)*@divider as int)
        END
        );
end

Upvotes: 0

JNevill
JNevill

Reputation: 50200

It sounds like numbers 0-50 get rounded up to "50", but any number larger than that should just get rounded to the nearest 50. Something like the following should work:

(CASE WHEN f1/50 < 1 THEN 1 ELSE ceiling(f1/50) END) * 50 AS rounded_to_50

Upvotes: 2

Related Questions