Round at specific decimal point

Question

Round at specific decimal point

giszzmo

SSChasing Mays

Points: 647
More actions
November 7, 2019 at 11:18 pm

#3696121

Hi, I am trying to round my numbers up to the nearest whole number if the decimal is equal to or greater than .7, for example 28.7 will be 29, or round down if it is below .7, for example, 28.6 will be 28. I'm not sure If I need to truncate first and make a temp table or if there is a function that will do this. If someone could point me in the right direction. Thanks!

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

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

gilbert delarosa SSC-Addicted Points: 425 More actions · Answer 1

this should do it. maybe make it into a Funtion

DECLARE 
@TestNumberdecimal(4,1) = 28.7
,@PreferredDecimaldecimal(4,1)= 0.7
;

SELECT 
CASE WHEN @TestNumber - FLOOR(@TestNumber) >= @PreferredDecimal THEN CEILING(@TestNumber) ELSE FLOOR(@TestNumber) END 
;

SET @TestNumber = 28.6;

SELECT 
CASE WHEN @TestNumber - FLOOR(@TestNumber) >= @PreferredDecimal THEN CEILING(@TestNumber) ELSE FLOOR(@TestNumber) END 
;

Phil Parkin SSC Guru Points: 246686 More actions · Answer 2

Will this work?

SELECT ROUND(@TestNumber - 0.2, 0);

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

Alternatively

SELECT ROUND(@TestNumber + 0.3, 0, 1);

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

giszzmo SSChasing Mays Points: 647 More actions · Answer 4

giszzmo

SSChasing Mays

Points: 647

November 8, 2019 at 10:35 pm

#3696419

Didn't work, but thanks.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 5

giszzmo wrote:

Didn't work, but thanks.

Here's my test script.

DECLARE @i DECIMAL(5, 3) = 0;
DECLARE @Inc DECIMAL(3, 2) = 0.01;

WHILE @i < 2
BEGIN
    PRINT CONCAT(@i, ', ', ROUND(@i + 0.3, 0, 1));

    SET @i = @i + @Inc;
END;

Now please show me yours.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

nigel. SSChampion Points: 11636 More actions · Answer 6

giszzmo wrote:

Didn't work, but thanks.

Which solution? And why?

What have you tried?

Nigel
How to post questions

giszzmo SSChasing Mays Points: 647 More actions · Answer 7

This was going to go into a LOAD statement using QlikView (which uses SQL syntax) but just didn't work the way that I wanted it to so I had to put it into an Expression (which uses Excel syntax), so it came out something like =if(FRAC(column1 >.71, ceil(column1),floor(column1))).

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

I wonder why people think that everything that uses SQL uses the same SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99126 More actions · Answer 9

giszzmo wrote:

This was going to go into a LOAD statement using QlikView (which uses SQL syntax)

But it doesn't use T-SQL. You've asked on a SQL Server community here, and the SQL dialect that SQL Server uses is T-SQL.

I have no idea what dialect QlikView SQL is based on; but I doubt it's T-SQL.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

david.wootton SSC Rookie Points: 25 More actions · Answer 10

declare @num decimal

set @num = 28.6 --28.7

select case when (@num * 10) % 10 >= 7

then

ceiling(@num)

else

floor(@num)

end

Thom A SSC Guru Points: 99126 More actions · Answer 11

david.wootton wrote:

declare @num decimal

decimal what? Where's your scale and precision?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

david.wootton SSC Rookie Points: 25 More actions · Answer 12

david.wootton

SSC Rookie

Points: 25

November 18, 2019 at 4:05 pm

#3698576

(38,0) default

Phil Parkin SSC Guru Points: 246686 More actions · Answer 13

david.wootton wrote:

(38,0) default

I think not. You should really test things before posting them here. Using a precision of 0 causes immediate rounding to the nearest whole number.

2019-11-18_11-38-46

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Thom A SSC Guru Points: 99126 More actions · Answer 14

Phil Parkin wrote:

david.wootton wrote:
(38,0) default
I think not. You should really test things before posting them here. Using a precision of 0 causes immediate rounding to the nearest whole number.

And this is exactly why I questioned you in the first place, David. It's so important that you declare your Length, Scales, and Precisions. Especially as the default values differ on the context omitted (i.e. a variable vs a column) and will cause unexpected results if undeclared.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk