SQL Server Performance Forum – Threads Archive

ROUND function

SELECT round(9.425000,2,1), round(34.775000,2,1) returns: 9.420000 34.770000 However I would like to use the same function for both values to return 9.420000 34.780000 (or 9.42 and 34.7<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Any suggestions? as always thanks all for your help [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] it’s all good

Is it this what you’re looking for? SELECT ROUND(MyNumber,2,1)
, CASE
WHEN ROUND(MyNumber * POWER( 10, 1 + 1 ) ,0,1) – (ROUND(MyNumber * POWER(10, 1 ) ,0,1) * 10) <= 5
THEN ROUND(MyNumber,2,1)
ELSE ROUND(MyNumber + 0.01, 2,1)
END
FROM
(SELECT 9.425000 UNION ALL SELECT 34.775000) AS t1 (MyNumber) ———- ———–
9.420000 9.420000
34.770000 34.780000 (2 row(s) affected)
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Where do you want to show data? If you use front end application, you can easily truncate leading zeroes Madhivanan Failing to plan is Planning to fail

Madhivanan, look closely at the values of the output again. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] It’s not about truncating anything. To me it seems like a variation of Bankers Rounding. – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Yes. <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by kingofSQL SELECT round(9.425000,2,1), round(34.775000,2,1) returns: 9.420000 34.770000 However I would like to use the same function for both values to return 9.420000 34.780000 (or 9.42 and 34.7<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Any suggestions? as always thanks all for your help [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] it’s all good <hr height="1" noshade id="quote"></blockquote id="quote"> I considered that red part [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Also I am not sure why first value is 9.420000 or 9.42 instead of 9.430000 or 9.43 Madhivanan Failing to plan is Planning to fail

Thanks FrankKalis/Madhivanan the code above works <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> fyi… My project is to store data in one place but output the SAME data to many front ends. (web, design tools, reports). When I compare the data between the web and the other front end I’m sometimes off by a penny because of the rounding. (My front end is Quark with Xcatalog extension to pull in the data via ODBC and Quark/Xcatalog does not have a code layer that I can perform an ETL type process.) However I’m one those folks who would rather do all of the ETL on the DATABASE BACKEND and not in the front end code to avoid this very situation <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> I was also told to try Modulus? This term describes the result when one number is divided by another number resulting in a remainder. For example 4 % 3 would result with a value of 1 since 1 is left over after 4 is divided by 3. 7%7 = 0 4%3 = 1 it’s all good

Go back to the raw values 9.425000 34.775000 The first has a digit < 5 before the last significant digit, the latter has a digit > 5 right before the last number. So, to me it looks as if the first should be rounded down, while the second needs to be rounded up. And that is some variation of Bankers Rounding. Not exactly, but pretty close. Bankers Rounding, however, is much simpler to implement. Just use STR(). [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Yes this is a variation of Bankers Rounding. CURSES Bankers Rounding has struck again!!!!! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> it’s all good

Oops, my reply was for Madhivanan. So, you found your solution? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Yes using your code but do you think a Bankers Rounding solution would work? it’s all good

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Oops, my reply was for Madhivanan. So, you found your solution? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Thanks Frank [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

hi, i’m searching for a way to do banker’s rounding
on a stored procedure, but i’m stuck on this
problem (follows the code). declare
@v1 as float,
@v2 as float set @v1 = 0.3650
set @v2 = 1.3650 print str(@v1, 10, 2)
print str(@v2, 10, 2) this code prints 0.36 and 1.37.
does anyone know why?
thanks in advance!

Read this thread which will you…
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=246556
Mohammed U.

Thank you Mohammed, In fact I’m using that function posted there
to do banker’s rounding, but did you saw that
that was happening using the str() function? it’s output varies depending on the whole
part of the number passed to it. thanks!

]]>