Incrementing decimal up to certain decimal then using next whole number in SQL

Question

I have a requirement to transform a decimal value and add .1 to it. My problem is that if this value would become a '.5' value, it instead needs to round up to the next whole number.

For example: If I have a value of '3.1' this would become '3.2' If I have a value of '5.3' this would become '5.4' If I have a value of '6.4' this would become '7.0'

I currently do this using a case statement so if the '.X' part is 4 it adds 0.6 else it adds 1, but is there a more efficient/better way of doing this?

Example of current code:

declare @Example Decimal (5,1) = '4.4'

select case when @Example like '%.4' then (@Example + '0.6')
else @Example + '0.1' end

Thanks!

Do you mean to assign strings to the decimal? Seems like a lot of extra conversion for SQL to do — n8wrl, Apr 23 '15 at 16:19
As you'll see from all the answers, there's no way to avoid doing a CASE statement. You might get some slight performance improvement from the suggestions though. — Tab Alleman, Apr 23 '15 at 16:59
Should it round up only when the fractional portion =0.4 or when >=0.4? — shawnt00, Apr 23 '15 at 19:06
@n8wrl It is possible that the values passed through will be strings, I would only want to modify values following the decimal format. — Adam, Apr 27 '15 at 14:23
@shawnt00 I only expect values up to .4, any others would be ignored. — Adam, Apr 27 '15 at 14:24

Stephan · Answer 1 · 2015-04-23T17:38:32.223

2

If it's a decimal, then you should work with functions made for decimals and don't convert to string whenever possible. Here's how to do it with modulus.

DECLARE @Table TABLE (nums DECIMAL(5,1))
INSERT INTO @Table
VALUES  (3.1),(5.3),(6.4)

SELECT  nums,
        CASE 
            WHEN nums % 1 < .4 THEN nums + .1
            ELSE CEILING(nums)
        END AS new_num
FROM @Table

Results:

nums                                    new_num
--------------------------------------- ---------------------------------------
3.1                                     3.2
5.3                                     5.4
6.4                                     7.0

edited Apr 23 '15 at 17:38

answered Apr 23 '15 at 16:35

Stephan

5,891
1
16
24

1

I think the `<= .3` should really be `< 0.4`. – Gordon Linoff Apr 23 '15 at 17:27
It doesn't really matter because the data type is DECIMAL(5,1) but I guess it might be bettter to change it – Stephan Apr 23 '15 at 17:38

HABO · Answer 2 · 2015-04-23T18:17:28.173

1

Assuming negative values aren't an issue:

declare @Samples as Table ( Sample Decimal(5,1) );
insert into @Samples
  values ( 0.0 ), ( 1.2 ), ( 2.4 ), ( 3.5 ), ( 4.6 ), ( 42.0 ), ( -1.2 ), ( -2.4 );
select Sample, Sample + 0.6 - 0.5 * Sign( Abs( Sample % 1.0 - 0.4 ) ) as [Result]
  from @Samples;

Not saying that it's a good idea, but it gets the work done.

edited Apr 23 '15 at 18:17

answered Apr 23 '15 at 18:12

HABO

15,314
5
39
57

Mark Leiber · Answer 3 · 2015-04-23T17:27:42.423

0

You can get the integer part (left side) using floor(x) and the decimal part using (x % 1). Then you can either add the two together (if the decimal part < .4) or add 1 to the integer part (if the decimal part >= .4):

select FLOOR(@Example) + (case when @Example % 1 >= .4 then 1 else @Example % 1 end)

edited Apr 23 '15 at 17:27

answered Apr 23 '15 at 16:37

Mark Leiber

3,118
2
13
22

The `0.5` should really be `0.4`. – Gordon Linoff Apr 23 '15 at 17:26

shawnt00 · Answer 4 · 2015-04-27T17:16:40.773

Note: I'm not sure all the answerers are handling fractional values the way you intended. My version treats 0.4 as the cutoff for rounding up. All values where the fractional component is between 0.4 and 0.9 are treated the same way.

Doing it without a CASE block is not impossible. I suspect this isn't the shortest expression either.

declare @t as table (d decimal(5, 1) not null);
insert into @t
values (2.0), (2.1), (2.2), (2.3), (2.4), (2.5), (2.6), (2.7), (2.8), (2.9);

select d, d + 0.1 + (round(-0.8 + d % 1, 0) + 1) * (0.9 - d % 1)
from @t;

I'm assuming the numbers are positive since you didn't specify behavior regarding negatives.

As for the other approach to rounding: this is algebraically equivalent to the other answer but the logic might be more obvious this way.

d + 0.1 + 0.5 * (1 - abs(sign(d % 1 - 0.4)))

Here's another expression built on functions. Of course coalesce does expand to a case expression internally.

floor(d) + 0.1 + coalesce(nullif(d % 1, 0.4), 0.9)

The problem with your approach is in the implicit casts. And in those casts you're assuming that dot character is always the decimal mark.

EDIT:

If you're sure that the fractional portion of the input never exceeds 0.4 then I can think of another easy calculation.

d + 0.1 + floor(d % 1 * 2.5) * 0.5

These little tricks were more useful before SQL platforms had case expressions. I think it's not likely that these options really have any efficiency benefit.

Incrementing decimal up to certain decimal then using next whole number in SQL

4 Answers4