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