Add Identity Increment per user

Hi
I have a Jobs Table: Job_ID,User_ID,Job_Info,...
Job_ID is the Primary Key.
I want to add [Job_ID_PerUser] so I wiil get:
Job_ID Job_ID_PerUser User_ID
1 1 A
2 1 B
3 1 C
4 2 A
5 3 A
6 2 C
Thanks

1) Set the key as primary key...

2) In the coloum properties there in Identity Specification

3) Set it to Yes

4) And set Increment Seed As 1

|||

I have a Primary Key with the Increment Seed=1 named [Job_ID]

I want another ID for a job [Job_ID_PerUser] that will start the count from 1 for every new user

and will increment by 1 every time this new user will add a job

|||

Assuming that you are using SQL Server, you can create the a SQL Server function the will calculate the next value of for the customer and add that as the column for the server.

CREATE FUNCTION dbo.GetCustomerMax (@.userIdint)RETURNSintASBEGINRETURN SELECT MAX(Job_ID_PerUser) + 1WHERE User_Id= @.userIdEND

In SQL Server Management Studio, you modify the table, add the column of type int.

Then create a stored procedure for the insertion of the record and call the GetCustomerMax function.

|||

Thanks it works greate.

do i need to put all of that in one transaction?