August 29, 2003 at 8:43 am
hello,
I have a table with the following fileds
Branch_number numeric
Invoice_number numeric identity
Is there any way to keep the squance of the invoice number depend of the branch_number.
example of what i mean is
Branch_number invoice_number
1 1
1 2
2 1
2 2
1 3
1 4
2 3
the increment of the identity has a relation with the branch.
August 29, 2003 at 9:45 am
I'm not sure that Identities are what you want. Basically an Identity column shoul only contain unique values (the number is invremented for each succesive record inserted) It's ideal for generating Primary Keys for example.
In your case neither of your columns will contain unique values (e.g multiple branch 1's and multiple invoice 1's etc.)
Withou knowing more I'd be inclined to explore using a stored procedure or a trigger to establish the relationship between the branch and the invoice. The problem you may then have is ensuring that the same number is not used twice for an invoice due to multi-user contention.
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
August 29, 2003 at 10:05 am
Try this example. Assuming you have branch number as input.
create table Test (branch int, invoice int)
declare @br int
select @br = 1
insert into Test
select @br,
case count(invoice)
when 0 then 1
else max(invoice) + 1
end
from Test where branch = @br
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply