ROUND numbers with steps

Question

ROUND numbers with steps

albanamino09

Valued Member

Points: 71
More actions
October 27, 2016 at 6:46 am

#318469

Hello,
I need to round some values in a column, with a step of 30.
For example,
every value from 0.1 to 29.9 to be rounded to 30,
every value from 30,1 to 59,9 to be round to 60,
every value from 60,1 to 89,9 to be round to 90, etc.
So, the values should be from 30,60,90,120,150 etc.
Many thanks!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

albanamino09 (10/27/2016)
Hello,
I need to round some values in a column, with a step of 30.
For example,
every value from 0.1 to 29.9 to be rounded to 30,
every value from 30,1 to 59,9 to be round to 60,
every value from 60,1 to 89,9 to be round to 90, etc.
So, the values should be from 30,60,90,120,150 etc.
Many thanks!

Quick suggestion

USE TEEST;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA (SD_ID,SD_VAL,SD_EXPECTED) AS

(

SELECT 1, 0.1, 30 UNION ALL

SELECT 1, 10.1, 30 UNION ALL

SELECT 1, 20.1, 30 UNION ALL

SELECT 1, 29.9, 30 UNION ALL

SELECT 1, 30.1, 60 UNION ALL

SELECT 1, 60.1, 90 --UNION ALL

)

SELECT

SD.SD_ID

,SD.SD_VAL

,SD.SD_EXPECTED

,((1 + FLOOR(SD.SD_VAL / 30)) * 30) AS ROUND_30

FROM SAMPLE_DATA SD;

Output

SD_ID SD_VAL SD_EXPECTED ROUND_30

------ -------- ----------- ---------

1 0.1 30 30

1 10.1 30 30

1 20.1 30 30

1 29.9 30 30

1 30.1 60 60

1 60.1 90 90

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Quick correction.

WITH SAMPLE_DATA (SD_ID,SD_VAL,SD_EXPECTED) AS

(

SELECT 1, 0.1, 30 UNION ALL

SELECT 1, 10.1, 30 UNION ALL

SELECT 1, 20.1, 30 UNION ALL

SELECT 1, 29.9, 30 UNION ALL

SELECT 1, 30.1, 60 UNION ALL

SELECT 1, 60.1, 90 UNION ALL

SELECT 1, 0, 0 UNION ALL

SELECT 1, 60, 60 --UNION ALL

)

SELECT

SD.SD_ID

,SD.SD_VAL

,SD.SD_EXPECTED

,CEILING(SD.SD_VAL / 30) * 30 AS ROUND_30

FROM SAMPLE_DATA SD;

Also, be aware that CEILING and FLOOR might behave in an unexpected way with negative numbers.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

Eirikur Eiriksson

SSC Guru

Points: 182948

October 27, 2016 at 8:56 am

#1909148

Thanks for the correction Luis!

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Here's an example of the different behavior of ROUND and CEILING/FLOOR.

WITH SAMPLE_DATA (SD_ID,SD_VAL,SD_EXPECTED) AS

(

SELECT 1, 0.1, 30 UNION ALL

SELECT 1, 10.1, 30 UNION ALL

SELECT 1, 20.1, 30 UNION ALL

SELECT 1, 29.9, 30 UNION ALL

SELECT 1, 30.1, 60 UNION ALL

SELECT 1, 60.1, 90 UNION ALL

SELECT 1, 0, 0 UNION ALL

SELECT 1, 60, 60 UNION ALL

SELECT 1, -0.1, -30 UNION ALL

SELECT 1, -10.1, -30 UNION ALL

SELECT 1, -20.1, -30 UNION ALL

SELECT 1, -29.9, -30 UNION ALL

SELECT 1, -30.1, -60 UNION ALL

SELECT 1, -60.1, -90 UNION ALL

SELECT 1, -0, 0 UNION ALL

SELECT 1, -60, -60

)

SELECT

SD.SD_ID

,SD.SD_VAL

,SD.SD_EXPECTED

,CEILING(SD.SD_VAL / 30) * 30 AS ROUND_30

,ROUND((SD.SD_VAL+(14.999999999*SIGN(SD.SD_VAL))) / 30, 0) * 30 AS ROUND_30

FROM SAMPLE_DATA SD;

Extra info: http://www.sqlservercentral.com/articles/T-SQL/145448/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Luis Cazares (10/27/2016)
Here's an example of the different behavior of ROUND and CEILING/FLOOR.
WITH SAMPLE_DATA (SD_ID,SD_VAL,SD_EXPECTED) AS
(
SELECT 1, 0.1, 30 UNION ALL
SELECT 1, 10.1, 30 UNION ALL
SELECT 1, 20.1, 30 UNION ALL
SELECT 1, 29.9, 30 UNION ALL
SELECT 1, 30.1, 60 UNION ALL
SELECT 1, 60.1, 90 UNION ALL
SELECT 1, 0, 0 UNION ALL
SELECT 1, 60, 60 UNION ALL
SELECT 1, -0.1, -30 UNION ALL
SELECT 1, -10.1, -30 UNION ALL
SELECT 1, -20.1, -30 UNION ALL
SELECT 1, -29.9, -30 UNION ALL
SELECT 1, -30.1, -60 UNION ALL
SELECT 1, -60.1, -90 UNION ALL
SELECT 1, -0, 0 UNION ALL
SELECT 1, -60, -60
)
SELECT
SD.SD_ID
,SD.SD_VAL
,SD.SD_EXPECTED
,CEILING(SD.SD_VAL / 30) * 30 AS ROUND_30
,ROUND((SD.SD_VAL+(14.999999999*SIGN(SD.SD_VAL))) / 30, 0) * 30 AS ROUND_30
FROM SAMPLE_DATA SD;
Extra info: http://www.sqlservercentral.com/articles/T-SQL/145448/

Luis, your expected return values for the negative values are wrong. Rounding negative values up doesn't go the direction you put the expected values. Rounding -30.1 up to the nearest 30 would go to -30 not -60 as -30 > -30.1. This is shown in your actual results.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Lynn Pettis (10/27/2016)
Luis Cazares (10/27/2016)
Here's an example of the different behavior of ROUND and CEILING/FLOOR.
Luis, your expected return values for the negative values are wrong. Rounding negative values up doesn't go the direction you put the expected values. Rounding -30.1 up to the nearest 30 would go to -30 not -60 as -30 > -30.1. This is shown in your actual results.

I would say that it depends. Rounding isn't fully defined and can follow different rules.

That's why I left both options to compare.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ROUND numbers with steps

Cookies on SQLServerCentral