We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

SQL - script to round decimalup  to the nearest integer - SQL Server 2005

Medium Priority
524 Views
Last Modified: 2012-05-11
Hello experts,

I have a table with column "level" that holds decimal values.  I have another column called new_number, that I would like to have the level value rounded up to the nearest whole number when the decimal is equal to or greater than .5.  I would like to round down to the nearest whole number if the level value is less than .5:

I currently have:

table: person
level          new_number
1.6            null
0.5            null
11.9            null
8.2            null

and I would like to end up with:


table: person
level          new_number
1.6            2
0.5            1
11.9          12
8.2            8

Something like:

-- first for rounding up
update person
set new_number = dec(round(level / 1, 0)*1,11,0)
where right(level,2) >= .5

-- then for rounding down
update person
set new_number = dec(round(level / 1, 0)*1,11,0)
where right(level,2) < .5

Thoughts?

Thanks!

-Dan
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Paul JacksonSoftware Engineer
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.