How to round to nearest nickel?

Question

Post reply

How to round to nearest nickel?

TC-416047

Ten Centuries

Points: 1312
More actions
June 17, 2008 at 1:08 pm

#188018

I need to do a price update.
The sales department wants the new prices rounded to the nearest nickel.
Has anyone already had to round to the nearest nickel?
It doesn't look like round() provides the options to do this like Excel does.
Any help would be appreciated.
tia,
Todd

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

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

This should get you there.

declare @amt decimal(18,4)

set @amt=1.277;

select @amt-

(cast(@amt*10000 as int)%500*1.0/10000)

+case when cast(@amt*10000 as int)%500>500-cast(@amt*10000 as int)%500

then 0.05 else .00 end

Edit: Had to adjust for 2000, since modulo isn't as flexible there.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

TC-416047 Ten Centuries Points: 1312 More actions · Answer 2

How about this?

I

update part

set

USER_4 = cast(convert(money,unit_price,0)as varchar), -- save old price

unit_price = round((unit_price*1.05)/0.05,0)*0.05 -- increase price and round

where

unit_price is not null and -- exclude null value

unit_price <> 0 -- exclude zero priced parts

I thought it was celver:>)

Found it here: http://blogs.msdn.com/jbesch/default.aspx

Sergiy SSC Guru Points: 110208 More actions · Answer 3

If "nickel" means 5c then this would be easier way:

ROUND(Amount/5, 2)*5

For proper precision Amount must be at least MONEY datatype, if not it must be converted to FLOAT first and result must be converted to appropriate datatype:

DECLARE @F FLOAT

SET @F = 5

UPDATE ...

SET Amount = ROUND(Amount/@F, 2)*@F

_____________
Code for TallyGenerator

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

Just in case anyone is having a doubt...

SELECT d.Amount, ROUND(d.Amount/5, 2)*5 AS Rounded

FROM (SELECT N/100.0 AS Amount FROM TALLY)d

Nicely done, Sergiy. Ya just gotta love simple. 😉

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

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Jeff Moden (6/17/2008)Ya just gotta love simple.

I'm just not a good typist. 😉

Apart from my laziness...

🙂

_____________
Code for TallyGenerator

Carl Federl One Orange Chip Points: 25384 More actions · Answer 6

Thanks Sergiy, as per all of your posts, a simple and elegent solution.

However, SQL Server has the resulting data type different that specified in BOL as "the result of ROUND to be same type as the numeric expression" but this is not occurring. From the below SQL batch run versus SQL Server 2000, 2005 and 2008:

For NUMERIC(8,2):

OriginalAmtnumeric82

RoundedAmtnumeric146

For NUMERIC(12,2):

OriginalAmtnumeric122

RoundedAmtnumeric186

Anybody have idea what is going on ?

selectOriginalAmt

,ROUND( OriginalAmt / 5 , 2 ) * 5 as RoundedAmt

intoRoundedResult

from(

select CAST( 1234.00 as NUMERIC(8,2)) union all select CAST( 1234.01 as NUMERIC(8,2)) union all

select CAST( 1234.02 as NUMERIC(8,2)) union all select CAST( 1234.03 as NUMERIC(8,2)) union all

select CAST( 1234.04 as NUMERIC(8,2)) union all select CAST( 1234.05 as NUMERIC(8,2)) union all

select CAST( 1234.06 as NUMERIC(8,2)) union all select CAST( 1234.07 as NUMERIC(8,2)) union all

select CAST( 1234.08 as NUMERIC(8,2)) union all select CAST( 1234.09 as NUMERIC(8,2))

) as Test (OriginalAmt)

selectsubstring(column_name,1,11) as column_name

,substring(data_type,1,10)as data_type

,numeric_precision

,numeric_scale

frominformation_schema.columns

wheretable_name = 'RoundedResult'

drop table RoundedResult

SQL = Scarcely Qualifies as a Language

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff Moden (6/17/2008)
Just in case anyone is having a doubt...
SELECT d.Amount, ROUND(d.Amount/5, 2)*5 AS Rounded
FROM (SELECT N/100.0 AS Amount FROM TALLY)d
Nicely done, Sergiy. Ya just gotta love simple. 😉

heh...yup. That's a good one. why travel around the planet when you can just cross the street...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Sergiy SSC Guru Points: 110208 More actions · Answer 8

Matt Miller (6/17/2008)why travel around the planet when you can just cross the street...

Because travel agencies tell you so...

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Sergiy (6/17/2008)
Matt Miller (6/17/2008)why travel around the planet when you can just cross the street...
Because travel agencies tell you so...

heh - I should take their advice...It's been a while..:)

It's just funny - I've been hassling with some crazy hierarchical FOR XML PATH thing all day, so I'm seeing complicated everywhere at this point. Just need to tape the KISS principle sticky note to my forehead again....:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

What if you need to do Banker's Rounding to the nearest 5 cents? :Whistling:

Sergiy SSC Guru Points: 110208 More actions · Answer 11

Carl Federl (6/17/2008)
However, SQL Server has the resulting data type different that specified in BOL as "the result of ROUND to be same type as the numeric expression" but this is not occurring.

Guess why they name division "floating point operation"?

Right, it's performed on floating point numbers.

In order to supply the decimal values to CPU (the part of it named "Floating Point Arithmetic Coprocessor") SQL Server must convert DECIMAL type values to one of true floating point type (REAL or FLOAT).

Output of the operation is gonna be the same type - REAL or FLOAT.

But because you supplied some freaky (for computing device) DECIMAL types SQL Server converts it to DECIMAL type matching result of the calculation by precision and scale.

Try this (again, very simple code, sorry 🙂 ):

declare @a decimal(3,0), @b-2 decimal(18,0)

SET @a = 3

SET @b-2 = 3

SELECT 1/@A, 1/@B

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 12

Matt Miller (6/17/2008) Just need to tape the KISS principle sticky note to my forehead again....:)

Then everybody else will read it but you...

:hehe:

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

Sergiy (6/17/2008)
Matt Miller (6/17/2008) Just need to tape the KISS principle sticky note to my forehead again....:)
Then everybody else will read it but you...
:hehe:

Actually - it was so I would see it when I woke up in the AM (in the mirror)...:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Sergiy (6/17/2008)

Here's one of my favorites to go along with that...

declare @a decimal(3,0), @b-2 decimal(18,0),@F FLOAT

SET @a = 3

SET @b-2 = 3

SET @F = 3

SELECT 1/@A*3.0, 1/@B*3.0,1/@F*3.0

SELECT 1/@A*3, 1/@B*3,1/@F*3

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