Round to next 1000 or 100 without Ceiling function

Question

Round to next 1000 or 100 without Ceiling function

parthi-1705

SSCrazy Eights

Points: 9277
More actions
December 13, 2010 at 11:55 am

#242396

Hi
I Need to round next 1000 with out using Ceiling function
print CEILING(12307.96 * 1.0 / 1000) * 1000----> 13000
print CEILING(2207.96 * 1.0 / 1000) * 1000 ---->3000
Thanks
Parthi
Thanks
Parthi

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

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

parthi-1705 (12/13/2010)
Hi
I Need to round next 1000 with out using Ceiling function
print CEILING(12307.96 * 1.0 / 1000) * 1000----> 13000
print CEILING(2207.96 * 1.0 / 1000) * 1000 ---->3000
Thanks
Parthi

If the CEILING function does what you what, why do you need to do somethig else?

If is doesn't, please explain exactly what you want.

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

But i want to know only CEILING can do this or some other functions can do this or not

Thanks

Parthi

Thanks
Parthi

GSquared SSC Guru Points: 260824 More actions · Answer 3

It's what ceiling is for. If you don't want to use it, you'll need to build a version of it. That won't be hard to do if you know math, but why re-invent a wheel? Or is this school work (that often has weird requirements like this)?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 4

I think you all got misunderstand, i just want any other options is there to get this apart from CEILING.Nothing else.If this is only option you peoples says then ok.

Thanks

Parthi

Thanks
Parthi

GSquared SSC Guru Points: 260824 More actions · Answer 5

Are you familiar with using Round() and adding .5 to the value?

DECLARE @Number DECIMAL(9, 2) ;

SELECT

@Number = 12300 ;

SELECT

@Number AS Original,

@Number / 1000 AS Div1000,

(@Number / 1000) + .5 AS Div1000Weighted,

ROUND((@Number / 1000) + .5, 0) AS Rounded,

ROUND((@Number / 1000) + .5, 0) * 1000 AS Final ;

Use the calculation in Final and you'll get a round-up. But it's more complex than just using Ceiling, and people who don't know the "add 1/2 and round" trick won't know what it's doing or how it works, which makes it require more documentation too. So, yes, there are other methods of getting this done, they're just worse that Ceiling is.

Note that the "add 1/2" trick can be broken by Float data calculations, because of rounding issues, in a few cases.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 6

Well if you don't want to hang from the ceiling, you could do a handstand on the floor.

SELECT

-1000 * FLOOR(-TESTDATA.Number / 1000.0),

1000 * CEILING(TESTDATA.Number / 1000.0)

FROM (

SELECT 12307.96 UNION ALL

SELECT 2207.96

) TESTDATA(Number)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

Here is another method - but you will have to test to determine which is most efficient. Look up the modulo operator in Books On Line to understand the "%"

DECLARE @Num INT

DECLARE @Chk INT

SET @Num = 3500

SET @Chk = @Num % 1000

IF @Chk >= 500

BEGIN

SET @Num = (@Num - @Chk) + 1000

END

ELSE

SET @Num = @Num - @chk

SELECT @Num

SET @Num = 2499

SET @Chk = @Num % 1000

IF @Chk >= 500

BEGIN

SET @Num = (@Num - @Chk) + 1000

END

ELSE

SET @Num = @Num - @chk

SELECT @Num

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 8

so Now i got Three options CEILING / FLOOR /ROUND

-->CEILING--- Returns the smallest integer greater than, or equal to, the specified numeric expression.

-->FLOOR--- Returns the largest integer less than or equal to the specified numeric expression

-->ROUND--- Returns a numeric value, rounded to the specified length or precision.

which to choose now for my data ROUND little bit confuse for people who sees first time but CEILING / FLOOR is ok but which to choose.Both are Mathematical Functions what is advantage here on choosing either one

Thanks

Parthi

Thanks
Parthi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Seriously?

Use the one most suited to what you're trying to do. You don't get prizes for the fanciest, most complex way of writing code.

If you're trying to round up to the nearest int (10, 100, etc), use CEILING. That's what it's there for!

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 10

GilaMonster (12/13/2010)
Seriously?
Use the one most suited to what you're trying to do. You don't get prizes for the fanciest, most complex way of writing code.
If you're trying to round up to the nearest int (10, 100, etc), use CEILING. That's what it's there for!

Yes, but apparently he's in a universe that never had Occam live in it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

GSquared (12/13/2010)
Are you familiar with using Round() and adding .5 to the value?
DECLARE @Number DECIMAL(9, 2) ;
SELECT
@Number = 12300 ;
SELECT
@Number AS Original,
@Number / 1000 AS Div1000,
(@Number / 1000) + .5 AS Div1000Weighted,
ROUND((@Number / 1000) + .5, 0) AS Rounded,
ROUND((@Number / 1000) + .5, 0) * 1000 AS Final ;
Use the calculation in Final and you'll get a round-up. But it's more complex than just using Ceiling, and people who don't know the "add 1/2 and round" trick won't know what it's doing or how it works, which makes it require more documentation too. So, yes, there are other methods of getting this done, they're just worse that Ceiling is.
Note that the "add 1/2" trick can be broken by Float data calculations, because of rounding issues, in a few cases.

I think this will also do that:

select Rnd = convert(int,round(12307.96+499.99,-3))

Result:

Rnd

-----------

13000

LutzM SSC Guru Points: 107049 More actions · Answer 12

Another option not using any of the ROUND/FLOOR/CEILING functions:

PRINT (CAST(12307.96 AS INT)/1000 + 1)*1000

Does it make sense to use it? I don't think so.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 13

Is the XQuery ceiling function allowed?

WITH TESTDATA(Number) AS (

SELECT 12307.96 UNION ALL

SELECT 2207.96

),

XMLData(data) AS (

SELECT Number AS "@Val"

FROM TESTDATA

FOR XML PATH('X'),ROOT('R'),TYPE

)

SELECT r.value('@Val','float') AS Number,

r.value('1000.0*ceiling(@Val*0.001)','float') AS RoundedNumber

FROM XMLData

CROSS APPLY data.nodes('/R/X') AS x(r);

Do I get a prize for the most obscure answer?

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

GSquared SSC Guru Points: 260824 More actions · Answer 14

LutzM (12/14/2010)
Another option not using any of the ROUND/FLOOR/CEILING functions:
PRINT (CAST(12307.96 AS INT)/1000 + 1)*1000
Does it make sense to use it? I don't think so.

That won't "round" correctly if the number is already divisible by 1000. Try it with 12000 instead of 12307.96.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon