Need a stored procedure to round off money field

I have a table with 3 fields

field1  int
field2 money
field3 money


field1 is primary key
field2 has various values say
12.63
75.12
46.44
27.89

Could someone suggest a stored procedure that would do the following
for each record check field2 and apply the following logic

If the cents ( that is number after decimal point eg in our case .63,.12,.44 and .89)

is <=12.5 then cents =0
<=37.5 but >12.5 the cents=25
<=62.5 but >37.5 then cents=50
<=87.5but >62.5 then cents=75
> 87.5 then  and cents=100 ( in this case add 1 to the dollar and make cents =0)

Then it should update field3 with the new rounded off values
It should NOT change field2
in our example fater running the stored procedure the value in field1 and field 2 should be. field1 which is primary key can be used for update

field2    field3
12.63   12.50
75.12   75.00
46.44   46.50
27.89   28.00
 
Thanks in advace for you help

mailcoldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
Is this SQL 2000?  If so, a user-defined function would be perfect for this.
0
mailcoldAuthor Commented:
Yes it is SQL-2000
0
Scott PletcherSenior DBACommented:
Never mind, a SP should be fine, and you could optionally pass a starting and ending field1 value.  Will post sample as soon as I can.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mailcoldAuthor Commented:
great
0
Scott PletcherSenior DBACommented:
Oops, trickier than I though, gotta' leave now, be back tomorrow.  Hopefully someone else can get to it more quickly.
0
mailcoldAuthor Commented:
Thanks for letting others know that they can have a go
0
mailcoldAuthor Commented:
Thanks for letting others know that they can have a go
0
namasi_navaretnamCommented:
Try this

drop function sp_RoundNumber
go
create function sp_RoundNumber
(
   @aDecimal numeric(18,2)
)
returns numeric(18,2)
AS
BEGIN

declare @vDecPart numeric(18,2),
        @vDecNew numeric(18,2),
        @vLeftNew numeric(18,2)


select @vDecPart = Convert(numeric(18,2), Right(Convert(varchar, @aDecimal), CharIndex('.', Convert(varchar, @aDecimal)) -1 )),
       @vLeftNew = Convert(numeric(18,2), Left(Convert(varchar, @aDecimal), CharIndex('.', Convert(varchar, @aDecimal)) -1 ))

SELECT @vDecNew = (CASE WHEN @vDecPart <= 12.5 then 0
                        WHEN @vDecPart <=37.5 and @vDecPart > 12.5 then 0.25
                        WHEN @vDecPart <=62.5 and @vDecPart >37.5 then 0.50
                        WHEN @vDecPart <= 87.5 and @vDecPart > 62.5 then 0.75
                        WHEN @vDecPart > 87.5 then  1.0
                     ELSE 0
                   END)

RETURN @vLeftNew + @vDecNew

END

To call,

insert into dectable values (11.4466)

select pubs.dbo.sp_RoundNumber(col1)
from dectable

0
mailcoldAuthor Commented:
created function and is fine
but attempt to run it is giving me error

update bob set caliberatedamount=sp_RoundNumber(Absoluteamount)

says

Server: Msg 195, Level 15, State 10, Line 1
'sp_RoundNumber' is not a recognized function name.

but sp_help displays the function



sp_help sp_RoundNumber

Name                                                                                                                             Owner                                                                                                                            Type                            Created_datetime                                      
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------
sp_RoundNumber                                                                                                                   dbo                                                                                                                              scalar function                 2003-11-19 08:09:06.977

======================================

What could be the problem ?
0
mailcoldAuthor Commented:
function is now working but not updating correctly see records marked with arrow <===

see results

       field2                     field3
       4.2600                4.0000 <====                .0000
       11.9900               12.0000               .0000
       12.6300               12.7500               .0000
       11.2700               11.2500               .0000
       9.6900                9.0000    <====            .0000
       11.2900               11.2500               .0000
       6.3700                6.0000   <====             .0000
      4.3900                4.0000   <===             .0000
       4.6500                4.0000  <===              .0000
0
namasi_navaretnamCommented:
You will need to fully qualify the function.
Ex: database.dbo.sp_RoundNumber

Need a minor correction

Install this function

drop function sp_RoundNumber
go
create function sp_RoundNumber
(
   @aDecimal numeric(18,2)
)
returns numeric(18,2)
AS
BEGIN

declare @vDecPart numeric(18,2),
        @vDecNew numeric(18,2),
        @vLeftNew numeric(18,2)


select @vDecPart = Convert(numeric(18,2), Right(Convert(varchar, @aDecimal), len(@aDecimal) - CharIndex('.', Convert(varchar, @aDecimal))  )),
       @vLeftNew = Convert(numeric(18,2), Left(Convert(varchar, @aDecimal), CharIndex('.', Convert(varchar, @aDecimal)) -1 ))

SELECT @vDecNew = (CASE WHEN @vDecPart <= 12.5 then 0
                        WHEN @vDecPart <=37.5 and @vDecPart > 12.5 then 0.25
                        WHEN @vDecPart <=62.5 and @vDecPart >37.5 then 0.50
                        WHEN @vDecPart <= 87.5 and @vDecPart > 62.5 then 0.75
                        WHEN @vDecPart > 87.5 then  1.0
                     ELSE 0
                   END)

RETURN @vLeftNew + @vDecNew

END

Changed to

@vDecPart = Convert(numeric(18,2), Right(Convert(varchar, @aDecimal), len(@aDecimal) - CharIndex('.', Convert(varchar, @aDecimal))  ))


Should work now.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
namasi_navaretnamCommented:
Once I installed the lastest function above I tried this and works well.

drop table n
go

create table n
(
 col1 money null,
 col2 money null
)

insert into n (col1) values (12.630)
insert into n (col1) values (9.690)
insert into n (col1) values (6.370)

update n
set col2 = pubs.dbo.sp_RoundNumber(col1) as numeric(18,2)

select * from n

HTH

Namasi
0
mailcoldAuthor Commented:
Its working fine thanks
0
mailcoldAuthor Commented:
Just want to check if this is a valid select statement
 

select col2 as select numeric(18,2)
from n
0
namasi_navaretnamCommented:
Sorry,

That should read
update n
set col2 = pubs.dbo.sp_RoundNumber(col1)

I initially had
update n
set col2 = Cast(pubs.dbo.sp_RoundNumber(col1) as numeric(18,2))

I removed the word "cast" but forgot to remove "as numeric(18,2)"


If varchar field with number then you can do

select cast(mycol as numeric(18,2))
from MyTable

where mycol is a varchar field that holds numbers.


This is my final statement that worked,
update n
set col2 = pubs.dbo.sp_RoundNumber(col1)
0
namasi_navaretnamCommented:
To answer that question, use cast

select CAST(col2 as select numeric(18,2))
from n

0
ImrichCommented:
Well, you are looking for proper rounding function to round to quarters. IMHO you don't need a stored procedure, just a simple round(field2 *4,0) / 4, which should give proper results. Given that your procedure would do just:
 update my_table set field3 =  round(field2 * 4, 0)/4

Mind you, I'd make field3 calculated - you won't need to run the proc all the time.

enjoy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.