Round to next 1000 or 100 without Ceiling function

Question

Round to next 1000 or 100 without Ceiling function

Viewing 10 posts - 31 through 39 (of 39 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Jeff Moden (12/15/2010)
Now.. comparing the two pieces of code above, which will be cheaper CPU wise? A floating point mulitply AND divide or a simple addition?

It's not often I disagree with Jeff, but:

ROUND is more computationally complex that CEILING (it cannot be done without a division, which ceiling doesn't require, and requires the interpretation of the third parameter to decide what to do and the second parameter which determines the scale [and that's a decimal scale, not a binary one, so handling it is far from trivial] neither of which is required for CEILING). The correct comparison is between a multiplication plus a division plus a computationally simple CEILING function on the one hand and an addition plus a computationally complex ROUND funtion (which includes a division) on the other hand. I wouldn't believe that one is faster than the other without extensive test results to support that, and I would expect the results to change (a) from release to release of the compiler and (b) from one piece of hardware to the next and (c) from one input type to the next.

So I would think it sensible for everyone to use whichever approach they found conceptually simpler, which will be ROUND for some people and CEILING for others, unless they were prepared to do the required measurement and repeat it and maybe change the code at each hardware engine upgrade and each new SQL release. (Actually I think I'm following Jeff's own approach here: if it might be important, measure it - don't just assume that what you think is fastestreally is fastest without measuring it to make sure; if you know for sure that it's not important, you don't have to bother.)

Tom

LutzM SSC Guru Points: 107049 More actions · Answer 2

To summarize Tom's statement: "It depends!" 😀 (Tom, correct me, if I'm wrong)

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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Based on code above, please remember that round(value, length, 1) will truncate, not round.

Here is possibly another method, but not tested to the extremes.

/* Non-Integer Data */

SELECT

TESTDATA.Number,

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

ROUND(TESTDATA.Number+499.9,-3,0) AS [USING_ROUND],

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

ROUND(TESTDATA.Number+499.9,-3) AS [USING_DEFAULT_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.1 UNION ALL

SELECT 1000.5

) TESTDATA(Number)

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

Lynn Pettis (12/16/2010)
Based on code above, please remember that round(value, length, 1) will truncate, not round.

Yep... I was counting on that but not on the other mistakes I made.

--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 5

@ Parthi, Andrew, and Tom,

Good Lord... My humble apologies. That's the second time I made such a mistake in this forum this week. And I really did it this time with two huge mistakes concerning negative numbers and decimal numbers.

It's not a good excuse but the reason for this mistake is that I went through all kinds of testing for another job (a long time ago) on guaranteed-to-be-positive integers and made the freakin' huge mistake of assuming without further testing (which is usually out of character for me) for a different problem.

I crossed out my reply on the post and added an edit telling people not to use the code because of the mistakes in it. :blush:

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Jeff,

Don't worry, mine doesn't fully work properly either. To make it work every time, you have to add enough 9's after the decimal point to fill the precision of the decimal value. Same thing would need to be done with your method.

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

Lynn Pettis (12/16/2010)
Jeff,
Don't worry, mine doesn't fully work properly either. To make it work every time, you have to add enough 9's after the decimal point to fill the precision of the decimal value. Same thing would need to be done with your method.

Oh I figured that out in a jiffy. 😛 The problem is that even with doing that mine does not correctly find the 1000 values for negative numbers.

--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)

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

Jeff Moden (12/16/2010)
@ Parthi, Andrew, and Tom,
Good Lord... My humble apologies. That's the second time I made such a mistake in this forum this week. And I really did it this time with two huge mistakes concerning negative numbers and decimal numbers.
It's not a good excuse but the reason for this mistake is that I went through all kinds of testing for another job (a long time ago) on guaranteed-to-be-positive integers and made the freakin' huge mistake of assuming without further testing (which is usually out of character for me) for a different problem.
I crossed out my reply on the post and added an edit telling people not to use the code because of the mistakes in it. :blush:

Jeff and others thank you very much Without you peoples it was not easy to get the functionality of ROUND and CIELING.This is what shows that each people thinks in different views and how we can achive the task, the forum here helps me and followers much more things to learn daily

Thank you for all your people for your time

Thanks

Parthi :-): kiss:

Thanks
Parthi

GSquared SSC Guru Points: 260824 More actions · Answer 9

So, basically, we've concluded that there are three ways to get this done:

The Ceiling way, which does it easily, simply, correctly, and efficiently

A wide number of Round ways which all have significant problems and may or may not produce correct results

Hyper-complex string manipulations that may or may not get the job done correctly and are certain to kill performance

So, yeah, I think Occam's vaporized particles just achieved escape velocity from all the centrifical force. 🙂

- 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

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

parthi-1705 (12/17/2010)
Jeff Moden (12/16/2010)
@ Parthi, Andrew, and Tom,
Good Lord... My humble apologies. That's the second time I made such a mistake in this forum this week. And I really did it this time with two huge mistakes concerning negative numbers and decimal numbers.
It's not a good excuse but the reason for this mistake is that I went through all kinds of testing for another job (a long time ago) on guaranteed-to-be-positive integers and made the freakin' huge mistake of assuming without further testing (which is usually out of character for me) for a different problem.
I crossed out my reply on the post and added an edit telling people not to use the code because of the mistakes in it. :blush:
Jeff and others thank you very much Without you peoples it was not easy to get the functionality of ROUND and CIELING.This is what shows that each people thinks in different views and how we can achive the task, the forum here helps me and followers much more things to learn daily
Thank you for all your people for your time
Thanks
Parthi :-): kiss:

Very generous feedback, Parthi. Thanks. 🙂

--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)