[RESOLVED] Help Needed to save continuous number based on 2 types on entries in a single table ?

[BlueFox]

SQL SERVER 2000:
I need to save 2 kinds of the same bill to a tables called bills. But each kind should have same continuous bill numbers.
Meaning if A and B are the two kinds, both should have continous bill numbers. But one type of bill should not have same bill number .Also should not skip a number too.
Example A should have 1,2,3... and B should have 1,2,3... They cannot have 1,1,2 for the same type. Also it should not skip a number like A:1,3,5 B:2,4 etc.
Note that I cannot take max of one type because it would cause same bill number when there are many users involved
How can I achieve this?T.Y.
LOOKS LIKE I POSTED IT IN WRONG CATEGORY. PLEASE MOVE IT TO DATABASE CATEGEORY.

[si_the_geek]

Thread moved to the 'Database Development' forum

[FunkyDexter]

Note that I cannot take max of one type because it would cause same bill number when there are many users involved

No it won't and this is, in fact, your answer. You just need to either wrap it in a transaction or, even better, select the max id as part of the insert statement. Something like this:-

Insert Into Table
(ID)
Select Max(ID) + 1
From Table

Because that's a single statement it will be resolved without any risk of interleaving with other users.