How to always round UP to next place value??

Question

Post reply

How to always round UP to next place value??

SQLFan-933088

Mr or Mrs. 500

Points: 549
More actions
May 9, 2016 at 12:57 pm

#326917

Hello,
I am writing a SP in which I need to take a bigint value and always round it up to the next place value.
I will not know the length/value of the number to be rounded up from until run time. The underlying datatype is bigint.
Maybe another way of describing what I need is to say I need to increment the place value by 1 and use the minimum value of that number.
It might help to think of it as an odometer in a vehicle where you want to know for the given current mileage, what is the next digit/place value that it's going to go up to.
Here are some examples of what I need to handle in the SP (adding commas for readability):
Source value (datatype bigint) ---> Desired 'rounded' value (datatype bigint):
129,995 --> 1,000,000
999,500,456 --> 1,000,000,000
999,100,000 --> 1,000,000,000
5,000 --> 10,000
I've tried the below but it doesn't work in the case of a value like 129,995 as the round function rounds it down to the closest 100,000.
declare @srcvalue bigint, @nextvalue bigint, @l int
set @srcvalue = 129995
set @l = CAST(LEN(@srcvalue) AS int)
set @nextvalue = ROUND(@srcvalue, -(@l - 1))
@nextvalue will equal 100,000, not the desired 1,000,000
Thanks for any suggestions! I've been banging my head on the wall with this for a while. :unsure:

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

ok, so are you rounding up to the nearest power of 10, then?

like this?

SELECT Power(CONVERT(BIGINT, 10), N) AS MaxVal

FROM (SELECT TOP 18 row_number()

OVER(

ORDER BY object_id) AS N

FROM sys.columns) MyAlias

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 2

Something like this?

CREATE TABLE #bigints (some_bigint bigint);

INSERT INTO #bigints VALUES

(129995),

(5000),

(100),

(999100876);

SELECT current_value=some_bigint,

next_place_value=POWER(10,CAST(LOG10(some_bigint) AS INT)+1)

FROM #bigints;

Cheers!

SQLFan-933088 Mr or Mrs. 500 Points: 549 More actions · Answer 3

Jacob - that looks like it's going to work! Thank you!!!! I will reply back if I have any issues putting into use. And I'll mark it as the answer if no issues.

You have no idea how relieved I am right now! 😀

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Something shorter, not sure if that means better. 😀

SELECT current_value=some_bigint,

next_place_value=POWER(10,CAST(LOG10(some_bigint) AS INT)+1),

next_place_value=POWER(10,LEN(some_bigint))

FROM #bigints;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

Luis Cazares (5/9/2016)
Something shorter, not sure if that means better. 😀
SELECT current_value=some_bigint,
next_place_value=POWER(10,CAST(LOG10(some_bigint) AS INT)+1),
next_place_value=POWER(10,LEN(some_bigint))
FROM #bigints;

Ah, nice. I immediately stuck with mathematical functions, and didn't consider going outside that box. I could bring the mathematical one closer by using FLOOR() instead of CAST(...AS INT), but the approach with LEN is still a nice way to save some keystrokes. 🙂

I'm curious to see if there's any discernible performance difference. Have to check that on a gigantic data set later.

Cheers!