round the value to the nearest

Question

Post reply

round the value to the nearest

Smash125

Hall of Fame

Points: 3562
More actions
September 16, 2014 at 4:22 am

#311617

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

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

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

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 1

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

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

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 2

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: 3562 More actions · Answer 3

thanks for the reply.

Can make it as below

34.000->34%

40.000->40%

35.000->35%

renanguilhem Valued Member Points: 64 More actions · Answer 4

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: 3562 More actions · Answer 5

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: 286590 More actions · Answer 6

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/