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!

• Eirikur Eiriksson

  SSC Guru

  Points: 182916

  More actions

  October 27, 2016 at 7:00 am

  #1909108

  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

  October 27, 2016 at 8:47 am

  #1909147

  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: 182916

  More actions

  October 27, 2016 at 8:56 am

  #1909148

  Thanks for the correction Luis!

  😎

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  October 27, 2016 at 8:58 am

  #1909149

  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

  October 27, 2016 at 9:23 am

  #1909153

  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

  October 27, 2016 at 9:26 am

  #1909156

  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