UFN_AUCTIONPACKAGE_CURRENCY_ROUND
Rounds an amount to be valid in a currency using the given rounding rule.
Return
| Return Type |
|---|
| money |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @AMOUNT | decimal(38, 9) | IN | |
| @DECIMALDIGITS | tinyint | IN | |
| @ROUNDINGTYPECODE | tinyint | IN |
Definition
Copy
create function dbo.UFN_AUCTIONPACKAGE_CURRENCY_ROUND
(
@AMOUNT decimal(38,9),
@DECIMALDIGITS tinyint,
@ROUNDINGTYPECODE tinyint
)
returns money
as begin
if @AMOUNT is null
return null;
-- Note that this code works differently than the VB code to avoid
-- premature rounding by SQL Server. (The VB decimal type is larger.)
declare @SCALE decimal (8,5);
declare @DONTROUNDPART decimal(36,4);
declare @ROUNDPART decimal(18,9);
declare @RESULT money;
if @ROUNDINGTYPECODE = 0 -- Half rounds away from zero
begin
set @SCALE = power(10.00000000000, -(@DECIMALDIGITS))
set @DONTROUNDPART = round(@AMOUNT, @DECIMALDIGITS, 9) -- truncate
set @ROUNDPART = @AMOUNT % @SCALE; -- get what's left
if @ROUNDPART >= (0.5 * @SCALE)
set @RESULT = (@DONTROUNDPART + @SCALE)
else if @ROUNDPART <= (-0.5 * @SCALE)
set @RESULT = (@DONTROUNDPART - @SCALE)
else
set @RESULT = @DONTROUNDPART
end
else if @ROUNDINGTYPECODE = 1 -- Half rounds to nearest even number
begin
set @SCALE = power(10.00000000000, -(@DECIMALDIGITS))
set @DONTROUNDPART = round(@AMOUNT, @DECIMALDIGITS, 9) -- truncate
set @ROUNDPART = @AMOUNT % @SCALE; -- get what's left
if @ROUNDPART > (0.5 * @SCALE)
set @RESULT = (@DONTROUNDPART + @SCALE)
else if @ROUNDPART < (-0.5 * @SCALE)
set @RESULT = (@DONTROUNDPART - @SCALE)
else if @ROUNDPART = (0.5 * @SCALE) or @ROUNDPART = (-0.5 * @SCALE)
set @RESULT = @DONTROUNDPART + (@DONTROUNDPART % (2*@SCALE))
else
set @RESULT = @DONTROUNDPART
end
else if @ROUNDINGTYPECODE = 2 -- Round 1 digit to nearest multiple of 5
begin
set @SCALE = power(10.00000000000, -(@DECIMALDIGITS - 1))
set @DONTROUNDPART = round(@AMOUNT, @DECIMALDIGITS - 1, 9) -- truncate
set @ROUNDPART = @AMOUNT % @SCALE; -- get what's left
if @ROUNDPART >= 0
begin
if @ROUNDPART >= (0.75 * @SCALE)
set @RESULT = (@DONTROUNDPART + @SCALE)
else if @ROUNDPART < (0.25 * @SCALE)
set @RESULT = @DONTROUNDPART
else
set @RESULT = (@DONTROUNDPART + (@SCALE * 0.5))
end
else
begin
if @ROUNDPART <= -(0.75 * @SCALE)
set @RESULT = (@DONTROUNDPART - @SCALE)
else if @ROUNDPART > (-0.25 * @SCALE)
set @RESULT = @DONTROUNDPART
else
set @RESULT = (@DONTROUNDPART - (@SCALE * 0.5))
end
end
else if @ROUNDINGTYPECODE = 3 -- Round 2 digits to nearest multiple of 50
begin
set @SCALE = power(10.00000000000, -(@DECIMALDIGITS - 2))
set @DONTROUNDPART = round(@AMOUNT, @DECIMALDIGITS - 2, 9) -- truncate
set @ROUNDPART = @AMOUNT % @SCALE; -- get what's left
if @ROUNDPART >= 0
begin
if @ROUNDPART >= (0.75 * @SCALE)
set @RESULT = (@DONTROUNDPART + @SCALE)
else if @ROUNDPART < (0.25 * @SCALE)
set @RESULT = @DONTROUNDPART
else
set @RESULT = (@DONTROUNDPART + (@SCALE * 0.5))
end
else
begin
if @ROUNDPART <= -(0.75 * @SCALE)
set @RESULT = (@DONTROUNDPART - @SCALE)
else if @ROUNDPART > (-0.25 * @SCALE)
set @RESULT = @DONTROUNDPART
else
set @RESULT = (@DONTROUNDPART - (@SCALE * 0.5))
end
end
else
begin
set @RESULT = null;
end
return @RESULT;
end