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
>

Allocate space

Hi,
I want to allocate 20 GB for a new database, what is the
best way of do it'
I simply allocate a .mdf file of 20 GB or create 4 of 5
GB ? i just have one RAID with 140 GB,the storage is
shared with other hosts, i have the servers's H:\ drive
pointing the storage with 70 GB for my databases in my
instance.
Thanks a lot
Miguel CorreiaI think it doesn't make much of a difference with 1 file or 4 files on the
same Raid disk ...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Miguel" <cmlcorreia@.netcabo.pt> wrote in message
news:0ab001c3a398$b410eb30$a501280a@.phx.gbl...
> Hi,
> I want to allocate 20 GB for a new database, what is the
> best way of do it'
> I simply allocate a .mdf file of 20 GB or create 4 of 5
> GB ? i just have one RAID with 140 GB,the storage is
> shared with other hosts, i have the servers's H:\ drive
> pointing the storage with 70 GB for my databases in my
> instance.
> Thanks a lot
> Miguel Correia

Allocate Range of AutoNumber ID's to each user

I have a non standard requirement that about 5 different users from differen
t
departments require sequential numbering for there records, however I want
all records to be in the one central table. This comes about as each
departments records ID's have a 4 letter acronym before the AutoNumber ID.
Is this possible or should I create seperate tables and then join them using
a view with union queries?
Any suggestions would be greatly appreciated.
ThanksHi
You can use INSTEAD OF TRIGGER to achieve this business logic
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Hi,
Not sure how to use this, do you have a link or sample I could work from.
Much appreciated.
"Chandra" wrote:
> Hi
> You can use INSTEAD OF TRIGGER to achieve this business logic
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Hi,
You can try this way
CREATE TRIGGER <TRIGGER_NAME>
ON <TABLE>
INSTEAD OF INSERT
AS
INSERT INTO <TABLE>
SELECT <your logic>, required columns
FROM INSERTED
WHERE INSERTED.Key = Key
GO
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Hi,
> Not sure how to use this, do you have a link or sample I could work from.
> Much appreciated.
> "Chandra" wrote:
>|||Thanks.
So best to set up my 5 tables which fulfills the business requirement and
then uses 'Instead of Triggers' to generate a master table with all results.
The only other query I have is how does the key part of the query work.
Thanks
"Chandra" wrote:
> Hi,
> You can try this way
> CREATE TRIGGER <TRIGGER_NAME>
> ON <TABLE>
> INSTEAD OF INSERT
> AS
> INSERT INTO <TABLE>
> SELECT <your logic>, required columns
> FROM INSERTED
> WHERE INSERTED.Key = Key
> GO
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "MarkCapo" wrote:
>|||Key is the primary key value in the table. If you are sure that you will be
inserting only one row at a time, then you can avoiding the key.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"MarkCapo" wrote:
> Thanks.
> So best to set up my 5 tables which fulfills the business requirement and
> then uses 'Instead of Triggers' to generate a master table with all result
s.
> The only other query I have is how does the key part of the query work.
> Thanks
> "Chandra" wrote:
>|||First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that make
s
no sense whatsoever. This is NOT a "non standard" requirement and is is quit
e
common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
mess of a system. This requirement is known as pre-allocated numbers. Most
banks I know do this.
I don't know what the numbers are for, so I'll assume Orders. Here's how
it's typically done:
TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
PROCEDURE Create_New_Order (@.Dept_Id,...) {
BEGIN TRANSACTION
@.Ord_Num =
SELECT Order_Num
FROM Avaiable_Order_Numbers
WHERE Dept_Id = @.Dept_Id
INSERT INTO Orders (@.Ord_Num, ...)
DELETE FROM Avaiable_Order_Numbers
WERE Order_Num = @.Order_Num
COMMIT
}
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MarkCapo" wrote:

> I have a non standard requirement that about 5 different users from differ
ent
> departments require sequential numbering for there records, however I want
> all records to be in the one central table. This comes about as each
> departments records ID's have a 4 letter acronym before the AutoNumber ID.
> Is this possible or should I create seperate tables and then join them usi
ng
> a view with union queries?
> Any suggestions would be greatly appreciated.
> Thanks|||Thanks for your assistance Alex. Looked at the Instead of Trigger option an
d
it seemed messy!
Basically if you use 5 tables one for each department with there own
sequential identity value incrementing concatenated with the Dept. acronym
and then put a trigger on Insert, Update (There is no delete facility) to
build a consolidated/master table for analysis, this will provide a robust
solution.
Any comments appreciated!
"Alex Papadimoulis" wrote:
> First, do not go with Chandra's advice of an INSTEAD OF TRIGGER -- that ma
kes
> no sense whatsoever. This is NOT a "non standard" requirement and is is qu
ite
> common -- hiding the business logic in an INSTEAD OF TRIGGER will create a
> mess of a system. This requirement is known as pre-allocated numbers. Most
> banks I know do this.
> I don't know what the numbers are for, so I'll assume Orders. Here's how
> it's typically done:
> TABLE Orders ( Order_Num, Cust_Num, Order_Date, ... )
> TABLE Avaiable_Order_Numbers ( Order_Num, Dept_Id )
> PROCEDURE Create_New_Order (@.Dept_Id,...) {
> BEGIN TRANSACTION
> @.Ord_Num =
> SELECT Order_Num
> FROM Avaiable_Order_Numbers
> WHERE Dept_Id = @.Dept_Id
> INSERT INTO Orders (@.Ord_Num, ...)
> DELETE FROM Avaiable_Order_Numbers
> WERE Order_Num = @.Order_Num
> COMMIT
> }
>
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "MarkCapo" wrote:
>

Allocate invoice numbers

At this point I don't know the terminology of what I need and would
appreciate help even getting started with researching the topic. I need to
allocate a series of (call them) invoice numbers. The "last used number" is
stored in a column of a table. I need to read the last used invoice number,
allocate a certain number of invoice numbers, and write the new "last used
invoice number" back to the table and column. My problem is that many users
will be producing invoices. How do I assure that only one user at a time
obtains an allocation of numbers and writes the last used back to the table?
Thank you.Hi
DECLARE @.par INT
BEGIN TRAN
SELECT @.par =MAX(invNumber)+1 FROM Table WITH (UPDLOCK,HOLDLOCK)
INSERT INTO Table (invNumber) VALUES (@.par)
COMMIT TRAN
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||I would use the identity property of the int column for this. The problem
with this is that you can't really do a range unless you want to use set
identity_insert on before doing inserts. Work arounds would consist of
adding a column which contains the user_ID and this way you could maintain
"uniqueness".
To get the last value of the inserted row use scope_identity()
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||richardb (richardb@.discussions.microsoft.com) writes:
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need
> to allocate a series of (call them) invoice numbers. The "last used
> number" is stored in a column of a table. I need to read the last used
> invoice number, allocate a certain number of invoice numbers, and write
> the new "last used invoice number" back to the table and column. My
> problem is that many users will be producing invoices. How do I assure
> that only one user at a time obtains an allocation of numbers and writes
> the last used back to the table?
There are two ways to go. One is to use the IDENTITY property, in which
case the table you mention would not be in play. In this case, you would
only insert into the target table, and pick the highest number with
scope_identity(). What is a little iffy here, is that I don't know whether
you actually can trust that if you insert 100 rows, that will be in a
contiguous range. But apart from that, the advantage with IDENTITY is that
it's good when there is plenty of concurrent access, as users will not
blocking with each other. Now, there is a price for this: if business
rules prohibits gaps in the numbers used, you cannot used IDENTITY. If
the INSERT fails, or the transaction is rolled back, those numbers will
not be reused later on, but are gone forever.
So that brings us to the other way, using your own table. The important
thing here is that you must hand the numbers in a transaction, and that
transaction must not commit until you have actually used them. The
idiom is like Uri showed:
BEGIN TRANSACTION
SELECT @.nextkey = coalesce(MAX(keycol), 0) + 1
FROM tbl WITH (HOLDLOCK, UPDLOCK)
WHERE ...
UPDATE tbl
SET keycol = @.nextkey + @.no_of_keys
WHERE ...
SELECT @.@.error = @.err
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
-- Use the keys
INSERT invoices (...)
..
SELECT @.@.error = @.err
IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
...
COMMIT TRANSACTION
The important thing is the locking hint UPDLOCK, HOLDLOCK. If two
users arrive to this spot about the same time, the who comes second
will be upheld at the SELECT statement, until the other process
commits. Another important thing is the rigorous error checking, so
that if there is an error, you rollback and release the numbers you
did not use. (The error handling can be done cleaner in SQL 2005.)
This solution gives no gaps, but it has poorer concurrency, as users
must for each other.
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|||Just curious -- while a transaction is being held in waiting what is or can
be displayed to the user interface of the application and by what mechanism?
<%= Clinton Gallagher
METROmilwaukee (sm) "A Regional Information Service"
NET csgallagher AT metromilwaukee.com
URL http://metromilwaukee.com/
URL http://clintongallagher.metromilwaukee.com/
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9737B6E65B08AYazorman@.127.0.0.1...
> richardb (richardb@.discussions.microsoft.com) writes:
> There are two ways to go. One is to use the IDENTITY property, in which
> case the table you mention would not be in play. In this case, you would
> only insert into the target table, and pick the highest number with
> scope_identity(). What is a little iffy here, is that I don't know whether
> you actually can trust that if you insert 100 rows, that will be in a
> contiguous range. But apart from that, the advantage with IDENTITY is that
> it's good when there is plenty of concurrent access, as users will not
> blocking with each other. Now, there is a price for this: if business
> rules prohibits gaps in the numbers used, you cannot used IDENTITY. If
> the INSERT fails, or the transaction is rolled back, those numbers will
> not be reused later on, but are gone forever.
> So that brings us to the other way, using your own table. The important
> thing here is that you must hand the numbers in a transaction, and that
> transaction must not commit until you have actually used them. The
> idiom is like Uri showed:
> BEGIN TRANSACTION
> SELECT @.nextkey = coalesce(MAX(keycol), 0) + 1
> FROM tbl WITH (HOLDLOCK, UPDLOCK)
> WHERE ...
> UPDATE tbl
> SET keycol = @.nextkey + @.no_of_keys
> WHERE ...
> SELECT @.@.error = @.err
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> -- Use the keys
> INSERT invoices (...)
> ...
> SELECT @.@.error = @.err
> IF @.err <> 0 BEGIN ROLLBACK TRANSACTION RETURN 1 END
> ...
> COMMIT TRANSACTION
> The important thing is the locking hint UPDLOCK, HOLDLOCK. If two
> users arrive to this spot about the same time, the who comes second
> will be upheld at the SELECT statement, until the other process
> commits. Another important thing is the rigorous error checking, so
> that if there is an error, you rollback and release the numbers you
> did not use. (The error handling can be done cleaner in SQL 2005.)
> This solution gives no gaps, but it has poorer concurrency, as users
> must for each other.
> --
> 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|||clintonG (csgallagher@.REMOVETHISTEXTmetromilwauke
e.com) writes:
> Just curious -- while a transaction is being held in waiting what is or
> can be displayed to the user interface of the application and by what
> mechanism?
For the user that runs the transaction, there are no resrictions. Anything
can be displayed.
For other users, the default behaviour is that they will be blocked if
they try to access data that is being changed by the transaction. There
are several ways around this:
o Use a LOCK TIMEOUT, so that they will get a message that the data is
not accessible.
o Use the NOLOCK hint in queries, which permits them to see uncommitted
data. This method is quite dangerous if you don't understand the
implications.
o Use the READPAST hint. With this hint, locked rows are simply skipped.
This method, too, have dangers, as users may get incorrect information.
o In SQL 2005, you can use snapshot isolation (which comes in two different
flavours). In this, case users will see a before-image of the updated
data, which is less likely to have issues than NOLOCK and READPAST.
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|||"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:%23mDS4pXCGHA.2920@.tk2msftngp13.phx.gbl...
> Just curious -- while a transaction is being held in waiting what is or
> can be displayed to the user interface of the application and by what
> mechanism?
>
If your application only uses a transaction to generate the invoice numbers
there will be no need to display anyting on the UI. The wait will be on the
10ms scale, and the user will never notice it. The problem starts when the
invoice generation code participates in larger, longer-lived transactions.
Then the waits will get longer. The absolutely worst part, however, is that
the wait experienced by any user is a product of the number of other users
on the system. Such a design may work acceptably with 5-10 users, but fail
with hundreds. That's the main reason why IDENTITY is the prefered
solution: it does not cause serialization waits, and won't bite you when you
try to scale your application.
David|||You would use a unique prefix/suffix
and his/her own series for each user.
For example:
user_name - avode, prefix - oa,
unique invoice_num - oa-1 (oa-2, oa-3 and so on);
user_name - richardb, prefix - r,
unique invoice_num - r-1 (r-2, r-3 and so on).
--
Odegov Andrey
avodeGOV@.mail.ru
(remove GOV to respond)
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||Here is the way I have set mine up, which I plan on using for invoice,
cheque, audit trail numbers etc. the 'Transtype' are predefined in the app
in my case VO2ADO, IE TR_ARINVOICENO = 'P'
Within my app
BeginTransaction()
at the proper row
IF USED = 1
tell the user to wait as someone else is updating
return to try the update again
ELSE
set USED = 1
NextInvoice = Next Number
Increment Next Number
endif
..
Do your updates etc
Now set the USED column in the Transnumber table to 0
IF all OK
Commit Transaction
Else
RollBack Transaction
end
I have not tested the speed using many operators, or table inserts and
updates.
Any comments on this methodology would be appreciaited.
DDL
CREATE TABLE [dbo].[TransNumber] (
[NextNumber] smallint DEFAULT(1) NOT NULL,
[Used] bit DEFAULT(0) NOT NULL,
[TransType] char(1) NOT NULL
)
GO
ALTER TABLE [dbo].[TransNumber] ADD CONSTRAINT [PK_TransactionNumber]
PRIMARY KEY CLUSTERED ([TransType])
GO
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 3, 0,
'A')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 9, 0,
'B')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'C')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'D')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'F')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'G')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 91,
0, 'H')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1006,
0, 'P')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'Q')
INSERT INTO [TransNumber] ([NextNumber], [Used], [TransType]) VALUES ( 1, 0,
'R')
When wise men disapprove, that's bad;
when fools applaud, that's worse.
A Spanish proverb
John Linville
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:44ED6E21-BDD1-4611-9C47-11B010E56222@.microsoft.com...
> At this point I don't know the terminology of what I need and would
> appreciate help even getting started with researching the topic. I need to
> allocate a series of (call them) invoice numbers. The "last used number"
> is
> stored in a column of a table. I need to read the last used invoice
> number,
> allocate a certain number of invoice numbers, and write the new "last used
> invoice number" back to the table and column. My problem is that many
> users
> will be producing invoices. How do I assure that only one user at a time
> obtains an allocation of numbers and writes the last used back to the
> table?
> Thank you.|||John Linville (orion^300@.telus.net) writes:
> Here is the way I have set mine up, which I plan on using for invoice,
> cheque, audit trail numbers etc. the 'Transtype' are predefined in the app
> in my case VO2ADO, IE TR_ARINVOICENO = 'P'
> Within my app
> BeginTransaction()
> at the proper row
> IF USED = 1
> tell the user to wait as someone else is updating
> return to try the update again
> ELSE
Really not sure how you intend to implement this, but, since the row
is locked, you will not be able to read USED, unless you use NOLOCK
to read it. Which may be fine for this particular case. Then again,
since two users could come here and read USED = 0, before any other
of them sets it to 1, there is a possible race condition here.
Rather than using an extra column, you are better of setting LOCK_TIMEOUT
to something >= 0, and if you get a lock-timeout error, then you tell
the user to wait.
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

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()
> )
>