round the value to the nearest

  • Smash125

    Hall of Fame

    Points: 3527

    I have a column called as NDM$ What I want do it round it the nearest value example I am giving below

    34.100->34%

    39.8->40

    35.4->35 some thing like that. please le me know how can I do that

  • dbafromthecold@gmail.com

    SSChampion

    Points: 10079

    There's a function in SQL Server called ROUND():-

    http://msdn.microsoft.com/en-us/library/ms175003.aspx

  • Eirikur Eiriksson

    SSC Guru

    Points: 182405

    Smash125 (9/16/2014)


    I have a column called as NDM$ What I want do it round it the nearest value example I am giving below

    34.100->34%

    39.8->40

    35.4->35 some thing like that. please le me know how can I do that

    Quick suggestion, use the round function and set the third parameter to 0.

    😎

    Example

    SELECT 34.100 AS IN_VAL,ROUND(34.100,0,0) AS OUT_VAL UNION ALL

    SELECT 39.8 AS IN_VAL,ROUND(39.8,0,0) UNION ALL

    SELECT 35.4 AS IN_VAL,ROUND(35.4,0,0)

    Results

    IN_VAL OUT_VAL

    ------- --------

    34.100 34.000

    39.800 40.000

    35.400 35.000

  • Smash125

    Hall of Fame

    Points: 3527

    thanks for the reply.

    Can make it as below

    34.000->34%

    40.000->40%

    35.000->35%

  • renanguilhem

    Valued Member

    Points: 64

    That should do it

    ;

    WITH

    CTE AS

    (

    SELECT CAST(ROUND(VAL,0,0) AS VARCHAR(10)) AS VAL

    FROM 'YOURTABLE'

    )

    SELECT SUBSTRING(VAL,1,2) + '%' AS NDM$

    FROM CTE

  • Smash125

    Hall of Fame

    Points: 3527

    LEFT(CAST(ROUND([Val],0,0) as nvarchar(10)),2) + '%' AS [val]

    to get the results some like this

    34.100 34.000 ->34%

    39.800 40.000 ->40%

    35.400 35.000 ->35%

    But this does not holds good when the percentage is 100% this holds when the percentage two characters. Can any body guide how to solve this

  • Sean Lange

    SSC Guru

    Points: 286496

    Smash125 (9/19/2014)


    LEFT(CAST(ROUND([Val],0,0) as nvarchar(10)),2) + '%' AS [val]

    to get the results some like this

    34.100 34.000 ->34%

    39.800 40.000 ->40%

    35.400 35.000 ->35%

    But this does not holds good when the percentage is 100% this holds when the percentage two characters. Can any body guide how to solve this

    The best way to solve this is leave the formatting to the front end. You should pass numeric values in this case to the front end. There the % can be added. If you turn everything into formatted strings the front end can't accurately sort or do any types of calculations without first removing the formatting.

    --EDIT--

    And why would you use nvarchar? Last time I checked all values for numbers will fit easily in the standard ASCII set so no need for the extended characters here.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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