SQL Server RoundFunctionSQL Up to nearest 10

jfewjypa  于 2023-03-17  发布在  其他
关注(0)|答案(5)|浏览(121)

I have searched for this and lot of resources available in the net. But i am struck with this simple question

Rounding int number to nearest high whole number

ex: 63  to 70
    71  to 80 

select ROUND(63,1) 
select ROUND(63,-1)

Appreciate your help

qvtsj1bj

qvtsj1bj1#

DECLARE @Table TABLE (nums DECIMAL(5,1))
INSERT INTO @Table
VALUES  (35),(53),(64)

SELECT  nums,
        (Convert(int,(nums/10)) + 1) * 10 AS Roundoff
FROM @Table

Result

nums Roundoff
35.0    40
53.0    60
64.0    70
mjqavswn

mjqavswn2#

You can't use ROUND directly for that, as there's no inbuilt mechanism to do so.

But you can easily just do it another way:

SELECT ( ( ( x - 1 ) / 10 ) + 1 ) * 10

This way you:

Subtract one divide by 10, discarding any rest beyond full 10s, meaning You'll turn 63 into 6 or 70 into 6 (this is important because you don't want to round 70 to 80, I presume. If you do, just don't subtract 1 before dividing). Then add one (this is the rounding up part) and multiply back up by 10.

If you need to use this a lot you can define a custom function to do it for you.

0s7z1bwu

0s7z1bwu3#

Another way:

DECLARE @t TABLE (X INT)
INSERT INTO @t (X) VALUES(30),(31),(33),(39),(43),(53),(63),(73)

Select X, round(X + 9, -1, 1) Rounded
FROM @t
+----+---------+
| X  | Rounded |
+----+---------+
| 30 |      30 |
| 31 |      40 |
| 33 |      40 |
| 39 |      40 |
| 43 |      50 |
| 53 |      60 |
| 63 |      70 |
| 73 |      80 |
+----+---------+
piah890a

piah890a4#

Best way is to use ceil, you can do:

SELECT ceil(amount / 10) * 10

For example, 50 would stay 50. 51 through 60 would be 60. 61 would be 70 and so on.

huwehgph

huwehgph5#

DECLARE @t TABLE (X INT)
INSERT INTO @t (X) VALUES(33),(43),(53),(63),(73)

Select floor((X + 9) / 10) * 10
FROM @t

Returns

40
50
60
70
80

相关问题