column formula

Hi everybody,

I have an company table and it has 2 columns, Company Code and User Code, I am incrementing "User Code" with "column identity" property of MS SQL Server 2K. I have different companies and those companies have different users. When I increment User Code one by one, of course it doesnt consider whether it is the same company or not.

Question 1: How can I satisfy this condition below?

EX:

company user
1---1
1---2
2---3
2---4
3---5
4---6

What I want is

company user
1---1
1---2
1---3
2---1
2---2
3---1
3---2
3---3

etc.


Question 2: I want to know that whether it is possible to do that by writing column formula or not?Question 1: How can I satisfy this condition below?
EX:
company user
1---1
1---2
2---3
2---4
3---5
What I want is
company user
1---1
1---2
1---3
2---1
2---2
3---1
Question 2: I want to know that whether it is possible to do that by writing column formula or not?

A1 One approach in supporting such a business requirement: one may implement a "key assignment" table that privately tracks and assigns user ID numbers for each company.

A2 It is not exactly clear what is meant by a "column formula"? However, the built in MS Sql Server 2k identity column support / functionality likely won't help much in implementing a typical "key assignment" table. (A "key assignment" table approach, as described in A1, would likely be implemented primarily using stored procedures / triggers, and / or user functions).|||/*
create table companyuser (
"user" int identity(1,1) primary key
,company int not null
)
*/

--ad 1
select
company
,newusernum=(select count(*) from companyuser cu2 where cu1.company=cu2.company and cu1."user"<=cu2."user")
,origusernum="user"
from companyuser cu1

--OR on large table
create table companyuserTMP (
"id" int identity(1,1) primary key
,origusernum int null
,company int not null
)
insert companyuserTMP(company,origusernum)
select company,"user"
from companyuser
order by company,"user"
select
tmp.company
,newusernum=tmp."id"-XXX."id"+1
,origusernum
from companyuserTMP tmp
join (
select "id"=min("id"),company
from companyuserTMP
group by company
) XXX on tmp.company=XXX.company
drop table companyuserTMP

--ad 2-- computed columns can use one row information only, use TR