Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Need a stored procedure to round off money field

Posted on 2003-11-18
Medium Priority
559 Views
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

0
Question by:mailcold
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 5
• 3
• +1

LVL 70

Expert Comment

ID: 9774925
Is this SQL 2000?  If so, a user-defined function would be perfect for this.
0

Author Comment

ID: 9774941
Yes it is SQL-2000
0

LVL 70

Expert Comment

ID: 9774943
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

Author Comment

ID: 9774967
great
0

LVL 70

Expert Comment

ID: 9774991
Oops, trickier than I though, gotta' leave now, be back tomorrow.  Hopefully someone else can get to it more quickly.
0

Author Comment

ID: 9775010
Thanks for letting others know that they can have a go
0

Author Comment

ID: 9775011
Thanks for letting others know that they can have a go
0

LVL 15

Expert Comment

ID: 9775155
Try this

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

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

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

Author Comment

ID: 9775225
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

Author Comment

ID: 9775256
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

LVL 15

Accepted Solution

namasi_navaretnam earned 1000 total points
ID: 9775360
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
(
)
returns numeric(18,2)
AS
BEGIN

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

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

Should work now.

0

LVL 15

Expert Comment

ID: 9775396
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

Author Comment

ID: 9775427
Its working fine thanks
0

Author Comment

ID: 9775972
Just want to check if this is a valid select statement

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

LVL 15

Expert Comment

ID: 9776080
Sorry,

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

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

LVL 15

Expert Comment

ID: 9776091
To answer that question, use cast

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

0

Expert Comment

ID: 9779079
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includâ€¦
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a paâ€¦
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
###### Suggested Courses
Course of the Month7 days, 22 hours left to enroll