UDF: prevent rounding off numbers

ckeller52
ckeller52 used Ask the Experts™
on
I have a UDF that is rounding numbers to the nearest whole number.  How can I prevent it?

The function is called from a sql view I am creating with several arguments passed to it and
it is to calculate the extended price for a line item on an order.
CREATE FUNCTION [dbo].[mtd_sale] 
(@inv_dt  dateTIME,
@curr_dt dateTime,
@line_type char(1),
@unit_price decimal(9,6),
@uom_ratio int,
@qty_ordered int,
@orig_ord_type char(1),
@discount_pct decimal(9,6),
@qty_to_ship int
)  
RETURNS decimal

BEGIN 
Declare @return_ext_price decimal(9,6)
Declare @dActualLineItemUnitPrice decimal(9,6)
Declare @dExtendedPrice decimal(9,6)

Set @dActualLineItemUnitPrice = 0
Set @dExtendedPrice = 0

if (datepart(month, @inv_dt) = datepart(month, @curr_dt) and datepart(year, @inv_dt) = datepart(year, @curr_dt) )

	IF @line_type =  'I'
		SET @dActualLineItemUnitPrice = Round((@unit_price * @uom_ratio),6)

	ELSE
		SET @dActualLineItemUnitPrice = Round(@unit_price, 6)


	IF (@qty_ordered < 0 or @orig_ord_type = 'C' )
		SET @dExtendedPrice = Round(((1 - (@discount_pct/100)) * (@qty_ordered * @dActualLineItemUnitPrice )),6)
	ELSE
		SET  @dExtendedPrice = Round(((1 - (@discount_pct/100)) * (@qty_to_ship * @dActualLineItemUnitPrice )),6)

	
	IF @orig_ord_type = 'C'
		SET @dExtendedPrice = Round( (@dExtendedPrice * (-1)),6)

SET @return_ext_price = Round(@dExtendedPrice,6)

RETURN  Round(@return_ext_price,6)
END

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
It's probably the INT parameters forcing your calculations to be evaluated using integer logic instead of decimal...

Try this:
CREATE FUNCTION [dbo].[mtd_sale] 
(@inv_dt  dateTIME,
@curr_dt dateTime,
@line_type char(1),
@unit_price decimal(9,6),
@uom_ratio int,
@qty_ordered int,
@orig_ord_type char(1),
@discount_pct decimal(9,6),
@qty_to_ship int
)  
RETURNS decimal

BEGIN 

Declare @return_ext_price decimal(9,6)
Declare @dActualLineItemUnitPrice decimal(9,6)
Declare @dExtendedPrice decimal(9,6)

Set @dActualLineItemUnitPrice = 0
Set @dExtendedPrice = 0

if (datepart(month, @inv_dt) = datepart(month, @curr_dt) and datepart(year, @inv_dt) = datepart(year, @curr_dt) )

	IF @line_type =  'I'
		SET @dActualLineItemUnitPrice = Round((@unit_price * convert(decimal(9,6), @uom_ratio)),6)

	ELSE
		SET @dActualLineItemUnitPrice = Round(@unit_price, 6)


	IF (convert(decimal(9,6), @qty_ordered) < 0 or @orig_ord_type = 'C' )
		SET @dExtendedPrice = Round(((1 - (@discount_pct/100)) * (convert(decimal(9,6), @qty_ordered) * @dActualLineItemUnitPrice )),6)
	ELSE
		SET  @dExtendedPrice = Round(((1 - (@discount_pct/100)) * (convert(decimal(9,6), @qty_to_ship) * @dActualLineItemUnitPrice )),6)

	
	IF @orig_ord_type = 'C'
		SET @dExtendedPrice = Round( (@dExtendedPrice * (-1)),6)

SET @return_ext_price = Round(@dExtendedPrice,6)

RETURN  Round(@return_ext_price,6)
END

Open in new window

Top Expert 2010

Commented:
Change:

RETURNS decimal

to

RETURNS decimal(9,6)
Top Expert 2010
Commented:
ckeller52,

BTW, if you use decimal(9,6), you will have a problem once you hit upon an input or a result that is >=1000 :)

Patrick
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Lammy82,
 I applied your code with Matthewspatrick suggestions and now I'm getting the error "Arithmetic overflow error converting numeric to data type numeric".
any ideas?

matthewspatrick,

What would be the best data type to store the extended_price calculation in this case?

Author

Commented:
Also,  can I drop the ROUND() functions as long as all the calculations involve decimal numbers? Or will that default to rounding off?
Top Expert 2010

Commented:
ckeller52,

How large do you need the numbers to be?  With decimal(9,6) you are saying you have 9 significant digits with
6 reserved for the decial places, and thus the largest number you can store is 999.999999.

The overflow error suggests you tried to use a number >= 1000.

I would use something like decimal(20,6).

You can also dispense with the ROUND().

Patrick

Author

Commented:
Suggested solution lead me to figure out what was wrong.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial