Round to next 1000 or 100 without Ceiling function

Question

Round to next 1000 or 100 without Ceiling function

Viewing 15 posts - 16 through 30 (of 39 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Mark-101232 (12/14/2010)
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?

It's still using "ceiling", just inside XML, not directly in SQL. I don't think that counts as avoiding "ceiling".

- 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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

For some reason straight integer divide didn't want to work here, I had to actually convert it on my 2k5. Wierdness.

declare @numeric decimal(32, 8)

set @numeric = 1.02

print ( CONVERT( bigint, @numeric) / 1000 ) * 1000 + 1000

SET @numeric = 4022.21341

print ( CONVERT( bigint, @numeric) / 1000 ) * 1000 + 1000

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

LutzM SSC Guru Points: 107049 More actions · Answer 3

GSquared (12/14/2010)
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.

Uupps, you're right... But easy to fix with a CASE statement 😉

PRINT (CAST(12001 AS INT)/1000 + (CASE WHEN 12001%1000=0 THEN 0 ELSE 1 END))*1000

Edit: @Craig: your solution suffers the same issue. But has a similar cure...

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]

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

PRINT (CAST(12001 AS INT)/1000 + (CASE WHEN 12001%1000=0 THEN 0 ELSE 1 END))*1000

Actual data is in float so we cant use modulo operator

Thanks

Parthi

Thanks
Parthi

LutzM SSC Guru Points: 107049 More actions · Answer 5

parthi-1705 (12/14/2010)
PRINT (CAST(12001 AS INT)/1000 + (CASE WHEN 12001%1000=0 THEN 0 ELSE 1 END))*1000
Actual data is in float so we cant use modulo operator
Thanks
Parthi

Then you'd just have to cast it to decimal or integer. Or use CEILING.

Now that you have several solutions on how to code around an existing function, would you please tell us, why you need to know? You told us you want to know if there are any other option but not why.

Btw: thank you for finally telling us the data format...

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]

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

Just ONLY one Solution is there or we can have multiple solutions Trying to learn more (on possibilities ) Not only on CEILING but on all functions to know how excatly it can be used.

Thanks

Parthi

Thanks
Parthi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

parthi-1705 (12/14/2010)
Just ONLY one Solution is there or we can have multiple solutions Trying to learn more (on possibilities ) Not only on CEILING but on all functions to know how excatly it can be used.
Thanks
Parthi

That's a good reason - to learn alternatives. Good luck

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 8

GSquared (12/14/2010)
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.

Or perhaps in a universe where Occam grew his beard and didn't shave?

Tom

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Tom.Thomson (12/15/2010)
GSquared (12/14/2010)
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.
Or perhaps in a universe where Occam grew his beard and didn't shave?

Speaking of Occam.... every one needs to lookup the ROUND function in Books Online and learn what happens when the second operand is a negative number. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 10

parthi-1705 (12/13/2010)
But i want to know only CEILING can do this or some other functions can do this or not
Thanks
Parthi

Lookup ROUND in Books Online and learn what happens when you use a negative number for length. It'll make all this hundreds and thousands rounding fit even for Occam. :w00t:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (12/15/2010)
parthi-1705 (12/13/2010)
But i want to know only CEILING can do this or some other functions can do this or not
Thanks
Parthi
CEILING is the way people do it becaue they don't know how to use the ROUND function. 😉 Lookup ROUND in Books Online and learn what happens when you use a negative number for length. It'll make all this hundreds and thousands rounding fit even for Occam. :w00t:

ROUND is great for rounding up/down at different precisions, but the OP wanted to round consistently upwards, for which ROUND is not well suited.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

andrewd.smith (12/15/2010)
Jeff Moden (12/15/2010)
parthi-1705 (12/13/2010)
But i want to know only CEILING can do this or some other functions can do this or not
Thanks
Parthi
CEILING is the way people do it becaue they don't know how to use the ROUND function. 😉 Lookup ROUND in Books Online and learn what happens when you use a negative number for length. It'll make all this hundreds and thousands rounding fit even for Occam. :w00t:
ROUND is great for rounding up/down at different precisions, but the OP wanted to round consistently upwards, for which ROUND is not well suited.

~~Ok... let's take a look at the code you posted...~~

~~1000 * CEILING(TESTDATA.Number / 1000.0)~~

~~Now let's take a look at the same problem with ROUND...~~

~~ROUND(TESTDATA.Number+999,-3,1)~~

~~I understand the doubt about ROUND compared to CEILING because most people don't know that ROUND has a 3rd operand. 😉 Look for it in Books Online, please.~~

~~Now.. comparing the two pieces of code above, which will be cheaper CPU wise? A floating point mulitply AND divide or a simple addition?~~

{edit} Hey folks... I crossed out my reply above because I was wrong. The code is bad an you shouldn't use it. I used a piece of code that I had used for "guaranteed-positive-integers" in the past and made the mistake of assuming it would work for negative numbers AND I didn't add the necessary .99 to the 999 to even make it work correctly for positive decimal numbers.

My apologies for any confusion I may have caused. I'm going to my room, now, so I can suck my thumb and twiddle my hair in private. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 13

There's another disadvantage to using CEILING for this problem... if you divide first using a NUMERIC number such as 1000.0 and the original number was an INT, the result won't be in the same datatype family as with ROUND. Matching datatypes can sometimes be hugely important for performance especially when trying to create SARGable predicates in a WHERE clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (12/15/2010)
andrewd.smith (12/15/2010)
Jeff Moden (12/15/2010)
parthi-1705 (12/13/2010)
But i want to know only CEILING can do this or some other functions can do this or not
Thanks
Parthi
CEILING is the way people do it becaue they don't know how to use the ROUND function. 😉 Lookup ROUND in Books Online and learn what happens when you use a negative number for length. It'll make all this hundreds and thousands rounding fit even for Occam. :w00t:
ROUND is great for rounding up/down at different precisions, but the OP wanted to round consistently upwards, for which ROUND is not well suited.
Ok... let's take a look at the code you posted...
1000 * CEILING(TESTDATA.Number / 1000.0)
Now let's take a look at the same problem with ROUND...
ROUND(TESTDATA.Number+999,-3,1)
I understand the doubt about ROUND compared to CEILING because most people don't know that ROUND has a 3rd operand. 😉 Look for it in Books Online, please.
Now.. comparing the two pieces of code above, which will be cheaper CPU wise? A floating point mulitply AND divide or a simple addition?

I agree that ROUND can be used reliably to round upwards to the nearest 1000 for positive integers, but your expression doesn't work for a dataset of mixed positive and negative numbers. Also it is not safe to use for non-integer values unless you know beforehand the precision of the numeric data p so you can choose an additive constant 1000 - q where q <= p. The OP's original post showed non-integer values in the examples.

/* Integer Data */

SELECT

TESTDATA.Number,

1000 * CEILING(TESTDATA.Number / 1000.0) AS [USING_CEILING],

ROUND(TESTDATA.Number + 999, -3, 1) AS [USING_ROUND]

FROM (

SELECT -1001 UNION ALL

SELECT -1000 UNION ALL

SELECT -999 UNION ALL

SELECT 999 UNION ALL

SELECT 1000 UNION ALL

SELECT 1001

) TESTDATA(Number)

Number USING_CEILING USING_ROUND

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

-1001 -1000 0

-1000 -1000 0

-999 0 0

999 1000 1000

1000 1000 1000

1001 2000 2000

/* Non-Integer Data */

SELECT

TESTDATA.Number,

1000 * CEILING(TESTDATA.Number / 1000.0) AS [USING_CEILING],

ROUND(TESTDATA.Number+999,-3,1) AS [USING_ROUND]

FROM (

SELECT -1000.5 UNION ALL

SELECT -1000.0 UNION ALL

SELECT -999.5 UNION ALL

SELECT 999.5 UNION ALL

SELECT 1000.0 UNION ALL

SELECT 1000.5

) TESTDATA(Number)

Number USING_CEILING USING_ROUND

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

-1000.5 -1000 .0

-1000.0 -1000 .0

-999.5 0 .0

999.5 1000 1000.0

1000.0 1000 1000.0

1000.5 2000 1000.0

TomThomson SSC Guru Points: 104773 More actions · Answer 15

andrewd.smith (12/16/2010)
Also it is not safe to use for non-integer values unless you know beforehand the precision of the numeric data p so you can choose an additive constant 1000 - q where q <= p.

Knowing the precision (a) is unneccessary and (b) is not helpful. You need the scale, not the precision. (In my view this terminology is confusing bunk, but that's what it says in BoL.)

Tom