Allocate by assets

I need to write a stored procedure in which I allocate based on the assets
of an account.
I'd like to pass my procedure 2 variables: @.AccountType and @.Amount. @.Amount
is the amount to be allocated. @.AccountType determines among which accounts
the amount will be allocated. The amount allocated to each account will be
determined by the "assets" of the account.
I need to account for rounding. The amount allocated should always be to 2
decimal places. If there is an unallocated "remainder" it should be
allocated among the accounts at random. It's critically important that the
amount to be allocated matches the amount allocated.
See DDL below:
If I was allocating 10.00 to all Accounts of AccountType 'A' and the assets
of AccountID 1 is 90.00 and AccountID 2 had assets of 10.00, Account 1 would
be allocated 9.00 and account 2 would get 1.00.
Logically, add the assets of all AccountTypes 'A' (100.00) and determine
each accounts percentage of the total. Account 1 has 90% of total and
Account 2 has 10% of total, then allocate based on these percentages.
Sample data and expected results for AccountType 'B'; Amount to be
allocated: 100.00 AccountID, AccountType,Assets,Expected Result
3,'B',33.35,17.33
4,'B',85.01,44.19
5,'B',74.02,38.48
Thanks to anyone who might help.
DROP TABLE Accounts
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountValue] [decimal](18, 2) NULL,
[Assets] [decimal](18, 2) NULL,
) ON [PRIMARY]
GO
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(1,'A',NULL,90)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(2,'A',NULL,10)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(3,'B',NULL,33.35)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(4,'B',NULL,85.01)
INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
(5,'B',NULL,74.02)here's one way, that i've used in the past:
create procedure AllocateValue
@.AccountType char(1),
@.Amount decimal(18,2)
as
set nocount on
-- Allocate the dollar amount based on asset pct
update Accounts
set AccountValue = @.Amount * (Assets/AccountTotal.AccountTypeTotal)
from Accounts
join (
select AccountType, sum(Assets) as AccountTypeTotal
from Accounts
where AccountType = @.AccountType
group by AccountType
) AccountTotal
on AccountTotal.AccountType = Accounts.AccountType
where Accounts.AccountType = @.AccountType
-- Adjust for rounding
-- adjust highest asset, highest account ID [if max asset matches]
update Accounts
set AccountValue =
AccountValue +
(@.Amount -
(select sum(AccountValue) from Accounts
where AccountType = @.AccountType))
where AccountType = @.AccountType
and AccountID = (
select Max(AccountID)
from Accounts
where AccountType = @.AccountType
and Assets = (select max(Assets)
from Accounts
where AccountType = @.AccountType
)
)
Terri wrote:
> I need to write a stored procedure in which I allocate based on the assets
> of an account.
> I'd like to pass my procedure 2 variables: @.AccountType and @.Amount. @.Amou
nt
> is the amount to be allocated. @.AccountType determines among which account
s
> the amount will be allocated. The amount allocated to each account will be
> determined by the "assets" of the account.
> I need to account for rounding. The amount allocated should always be to
2
> decimal places. If there is an unallocated "remainder" it should be
> allocated among the accounts at random. It's critically important that the
> amount to be allocated matches the amount allocated.
> See DDL below:
> If I was allocating 10.00 to all Accounts of AccountType 'A' and the asset
s
> of AccountID 1 is 90.00 and AccountID 2 had assets of 10.00, Account 1 wou
ld
> be allocated 9.00 and account 2 would get 1.00.
>
> Logically, add the assets of all AccountTypes 'A' (100.00) and determine
> each accounts percentage of the total. Account 1 has 90% of total and
> Account 2 has 10% of total, then allocate based on these percentages.
>
> Sample data and expected results for AccountType 'B'; Amount to be
> allocated: 100.00 AccountID, AccountType,Assets,Expected Result
>
> 3,'B',33.35,17.33
> 4,'B',85.01,44.19
> 5,'B',74.02,38.48
>
> Thanks to anyone who might help.
>
> DROP TABLE Accounts
>
> CREATE TABLE [dbo].[Accounts] (
> [AccountID] [int] NOT NULL ,
> [AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AccountValue] [decimal](18, 2) NULL,
> [Assets] [decimal](18, 2) NULL,
> ) ON [PRIMARY]
> GO
>
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (1,'A',NULL,90)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (2,'A',NULL,10)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (3,'B',NULL,33.35)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (4,'B',NULL,85.01)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue,Asse
ts) VALUES
> (5,'B',NULL,74.02)
>
>|||Trey Walpole (treypole@.newsgroups.nospam) writes:
> -- Adjust for rounding
> -- adjust highest asset, highest account ID [if max asset matches]
> update Accounts
> set AccountValue =
> AccountValue +
> (@.Amount -
> (select sum(AccountValue) from Accounts
> where AccountType = @.AccountType))
> where AccountType = @.AccountType
> and AccountID = (
> select Max(AccountID)
> from Accounts
> where AccountType = @.AccountType
> and Assets = (select max(Assets)
> from Accounts
> where AccountType = @.AccountType
> )
> )
Since Terri said that the rounding should be allocated to an account chosen
at random, here is a variation that does this:
update Accounts
set AccountValue =
AccountValue +
(@.Amount -
(select sum(AccountValue) from Accounts
where AccountType = @.AccountType))
where AccountType = @.AccountType
and AccountID = (
select TOP 1 AccountID
from Accounts
where AccountType = @.AccountType
ORDER BY newid()
)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||ah yes - missed that random bit
Erland Sommarskog wrote:
> Trey Walpole (treypole@.newsgroups.nospam) writes:
>
>
> Since Terri said that the rounding should be allocated to an account chose
n
> at random, here is a variation that does this:
> update Accounts
> set AccountValue =
> AccountValue +
> (@.Amount -
> (select sum(AccountValue) from Accounts
> where AccountType = @.AccountType))
> where AccountType = @.AccountType
> and AccountID = (
> select TOP 1 AccountID
> from Accounts
> where AccountType = @.AccountType
> ORDER BY newid()
> )
>