Round at specific decimal point
-
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!
-
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
; -
Will this work?
SELECT ROUND(@TestNumber - 0.2, 0);
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Alternatively
SELECT ROUND(@TestNumber + 0.3, 0, 1);
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Didn't work, but thanks.
- 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.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
-
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))).
-
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) - 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 -
declare @num decimal
set @num = 28.6 --28.7
select case when (@num * 10) % 10 >= 7
then
ceiling(@num)
else
floor(@num)
end
-
(38,0) default
- 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.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - 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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply