Rounding of Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Rounding of Problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-03 : 10:41:13

Muneyi writes "Rounding off Error

I have to write a function to make sure money shared between agents adds up to the original
balance shared. I have a problem with sharing odd numbers between agents
that share money equally for example

My current formula is rolepercent * Amount to be shared

The problem occurs when I share 10.67 between 2 agents with role percent 50% because when
the system rounds 5.335 I get 5.34 which will add back to 10.68 which is wrong.

I want to be able to let the funtion give 5.34 to one and 5.33 to the other so that it adds back to 10.67

Example 2 if I am sharing 100 between 3 The share should be as follows

33.33 ,33.33 and 33.34

In essence the funtion should accept the following parameters

SET @NumberOfAgents = 3
SET @Balance =100
SET @AgentLevel=1 -- if agent level is 1 you get the maximum value in this case level 1 gets 33.34
SET @RolePercent = 0.5
SET @ShareType = 1 -- if user has the following percents to share with 3 agents

0.5 , 0,3 and 0.2 then share type is 2 and if share type is 1 it means they are sharing equally.

My small function is below , however I cannot proceed from there .

/*
Title: OUT OF BALANCE W/SPLIT AGENTS

Description: An out of balance occurs on IPAS accounts that have
split agents and odd dollar amounts. It appears that dollar
amounts are split 50/50 without one being adjusted by 1 to balance back
to original dollar amount.

Purpose : Function has been created to solve problem described above .
It allows users to enter the number of agents and the balance to
be split and level/priority of the agent. It returns the balance per agent

Created By: Muneyi on 03/31/2004

Modification History:
* UserID Date Reason For Change
**************************************************************************************

--DROP FUNCTION fncSplitAgentBalance
CREATE FUNCTION fncSplitAgentBalance
( --Input Parameters
@NumberOfAgents INT ,
@Balance DECIMAL(18,10) ,
@AgentLevel INT ,
@RolePercent DECIMAL(9,6),
@ShareType INT
)

RETURNS DECIMAL(18,10)

AS

BEGIN --begin function
*/
SET NOCOUNT ON

DECLARE @Count INT ,
@AmtShare DECIMAL(18,10) ,
@lastbal DECIMAL(18,10),
@ValuationAmount DECIMAL(18,10) ,
@NumberOfAgents INT ,
@Balance DECIMAL(18,10) ,
@AgentLevel INT ,
@RolePercent DECIMAL(9,6) ,
--Helps to distinguish between the way funds are shared 1
--For same rolepercent and 2 for variable rolepercent
@ShareType INT

SET @NumberOfAgents = 3
SET @Balance =100
SET @AgentLevel=1
SET @RolePercent = 0.5
SET @ShareType = 2

IF @RolePercent = 0.5 and @ShareType = 1
BEGIN

--IF @NumberOFAgents > = 1

--BEGIN

--begin if
SET @Count=1
SET @AmtShare = @Balance/ @NumberOfAgents

DECLARE @Balances Table(AmtShare DECIMAL(18,10))

INSERT @Balances Select @AmtShare
--SELECT AmtShare = @AmtShare INTO @Balances

WHILE @Count < @NumberOfAgents - 1
BEGIN
SET @Count= @Count+ 1
INSERT @Balances SELECT @AmtShare
END

SET @lastbal = @Balance - (@amtshare * ( @NumberOfAgents-1))

INSERT @Balances SELECT @lastbal

DECLARE @ShareAmount Table(AmtShare DECIMAL(18,10) , Agentlevel int )

INSERT @ShareAmount
SELECT b.* ,AgentLevel= Case When @Amtshare > @lastbal then 2
else 1 end-- AS AgentLevel
FROM @Balances b ORDER BY AmtShare DESC
--assign result over here
If

gpl
Posting Yak Master

195 Posts

Posted - 2004-05-03 : 10:55:20

Something I did when I had to make things balance is to share (Amount / Num of Agents) between n-1 agents, then assign the remainder (Amount - (Amount / Num of Agents)) to the last - you could randomly choose the agent to get the remainder so that it is reasonably fair.

Sorry I dont have any code to share - it was not done in SQL
Graham

nr
SQLTeam MVY

12543 Posts

Posted - 2004-05-03 : 11:22:10

You can do something like this. Don't know what form your data is in but you can probably do the n-1 agents without iteration.

declare @tbl table (id int, val decimal(18,2))
set @i = 0
while @i < @noagents - 1
begin
select @i = @i + 1
insert @tbl select @i, @bal/@noagents
end

insert @tbl select @i, @bal - (select sum(val) from @tbl)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources