Round at specific decimal point

  • giszzmo

    SSChasing Mays

    Points: 647

    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!

  • gilbert delarosa

    SSC-Addicted

    Points: 415

    this should do it. maybe make it into a Funtion

    DECLARE 
    @TestNumber decimal(4,1) = 28.7
    ,@PreferredDecimal decimal(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: 244445

    Will this work?

    SELECT ROUND(@TestNumber - 0.2, 0);

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin

    SSC Guru

    Points: 244445

    Alternatively

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • giszzmo

    SSChasing Mays

    Points: 647

    Didn't work, but thanks.

  • Phil Parkin

    SSC Guru

    Points: 244445

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • nigel.

    SSChampion

    Points: 11624

    giszzmo wrote:

    Didn't work, but thanks.

    Which solution? And why?

    What have you tried?

  • giszzmo

    SSChasing Mays

    Points: 647

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

    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)
    Forum FAQ

  • Thom A

    SSC Guru

    Points: 98622

    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.

  • david.wootton

    Grasshopper

    Points: 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: 98622

    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.

  • david.wootton

    Grasshopper

    Points: 10

    (38,0) default

  • Phil Parkin

    SSC Guru

    Points: 244445

    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 the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thom A

    SSC Guru

    Points: 98622

    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.

    2019-11-18_11-38-46

    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.

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

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