Solved

Need a stored procedure to round off money field

Posted on 2003-11-18
17
548 Views
Last Modified: 2012-06-21
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
Comment
Question by:mailcold
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 69

Expert Comment

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

Author Comment

by:mailcold
ID: 9774941
Yes it is SQL-2000
0
 
LVL 69

Expert Comment

by:ScottPletcher
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

by:mailcold
ID: 9774967
great
0
 
LVL 69

Expert Comment

by:ScottPletcher
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

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

Author Comment

by:mailcold
ID: 9775011
Thanks for letting others know that they can have a go
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9775155
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:mailcold
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

by:mailcold
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

by:
namasi_navaretnam earned 250 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
(
   @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
 
LVL 15

Expert Comment

by:namasi_navaretnam
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

by:mailcold
ID: 9775427
Its working fine thanks
0
 

Author Comment

by:mailcold
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

by:namasi_navaretnam
ID: 9776080
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9776091
To answer that question, use cast

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

0
 

Expert Comment

by:Imrich
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now