Allocate to records, randomize rounded amount

I'd like to build a stored procedure that would allocate a single value
among records. If there was a rounded remainder I would like to randomly
pick one of the records to receive the "extra" rounded amount. I'd like to
pass my procedure 2 variables: @.AccountType and @.Amount. The amount
allocated should always be to 2 decimal places.
If the variables were @.AccountType = "A' and @.Amount = 10 the AccountValue
for both AccountIDs 1 and 2 would be 5.00
If the variables were @.AccountType = "A' and @.Amount = 11 the AccountValue
for both AccountIDs 1 and 2 would be 5.50
If the variables were @.AccountType = "B' and @.Amount = 10 the AccountValue
would be 3.33 for 2 of the accounts and 3.34 for the third account. I'd like
to randomize which account gets 3.34, the extra penny.
Can anyone help me write this stored procedure?
CREATE TABLE [dbo].[Accounts] (
[AccountID] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AccountValue] [decimal](18, 2) NULL
) ON [PRIMARY]
GO
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(1,'A',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(2,'A',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(3,'B',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(4,'B',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(5,'B',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(6,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(7,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(8,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(9,'C',NULL)
INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
(10,'C',NULL)e.g.
alter procedure AllocateToAccounts(@.AccountType char(1), @.TotalValue
decimal(18,2))
as
declare
@.Allocation decimal(18,2),
@.Count int, @.RandID int
select @.Count = count(*)
from Accounts
where AccountType = @.AccountType
select @.RandID = (
select top 1 AccountID
from Accounts
where AccountType=@.AccountType
order by NewID()
)
select @.Allocation = @.TotalValue / @.Count
update Accounts
set AccountValue =
case AccountID
when @.RandID then @.Allocation + (@.TotalValue-(@.Allocation*@.Count))
else @.Allocation
end
where AccountType = @.AccountType
Terri wrote:
> I'd like to build a stored procedure that would allocate a single value
> among records. If there was a rounded remainder I would like to randomly
> pick one of the records to receive the "extra" rounded amount. I'd like to
> pass my procedure 2 variables: @.AccountType and @.Amount. The amount
> allocated should always be to 2 decimal places.
> If the variables were @.AccountType = "A' and @.Amount = 10 the AccountValue
> for both AccountIDs 1 and 2 would be 5.00
> If the variables were @.AccountType = "A' and @.Amount = 11 the AccountValue
> for both AccountIDs 1 and 2 would be 5.50
> If the variables were @.AccountType = "B' and @.Amount = 10 the AccountValue
> would be 3.33 for 2 of the accounts and 3.34 for the third account. I'd li
ke
> to randomize which account gets 3.34, the extra penny.
> Can anyone help me write this stored procedure?
> CREATE TABLE [dbo].[Accounts] (
> [AccountID] [int] NOT NULL ,
> [AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AccountValue] [decimal](18, 2) NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (1,'A',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (2,'A',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (3,'B',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (4,'B',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (5,'B',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (6,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (7,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (8,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (9,'C',NULL)
> INSERT INTO Accounts (AccountID,AccountType,AccountValue) VALUES
> (10,'C',NULL)
>|||Great, thanks, works perfectly. I have a related allocation method that I
need a stored procedure for so I am going to continue this thread and hope
for the continued expertise and generosity of this group.
I've modified the structure of my table to include an additional field,
Assets. In this method I want to allocate based on the assets of an account.
If I was allocating to 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.
In this method I don't want to assign the rounded amount randomly but
instead based on "assets". If the number is rounded up it should be
allocated to the account with the most assets. If the number is allocated
down it should go to the account with the least assets.
My actual asset figures are in the millions so ties would be extremely
unlikely.
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> wrote in message
news:#3xiFQEAGHA.2656@.tk2msftngp13.phx.gbl...
> e.g.
> alter procedure AllocateToAccounts(@.AccountType char(1), @.TotalValue
> decimal(18,2))
> as
> declare
> @.Allocation decimal(18,2),
> @.Count int, @.RandID int
> select @.Count = count(*)
> from Accounts
> where AccountType = @.AccountType
> select @.RandID = (
> select top 1 AccountID
> from Accounts
> where AccountType=@.AccountType
> order by NewID()
> )
> select @.Allocation = @.TotalValue / @.Count
> update Accounts
> set AccountValue =
> case AccountID
> when @.RandID then @.Allocation + (@.TotalValue-(@.Allocation*@.Count))
> else @.Allocation
> end
> where AccountType = @.AccountType
>