Click here to Skip to main content
15,070,511 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables, departments (dept_ID, dept_Name, dept_ShortName), and employees (emp_ID, emp_Name, emp_Code).
Then when add new employee become emp_Code from dept_ShortName(varchar) and emp_ID(int) like HR_001. and when add new again become HR_002 ect. and doing that in all departments like sls_001, sls_002... in same column emp_Code.
how it search in column emp_Code to last employee's department and add new +1?

What I have tried:

i tried to join data in one column and add auto number to each department but i can not, because i am beginner in Sql and c#
Posted
Updated 19-Mar-21 0:01am
Comments
Maciej Los 19-Mar-21 4:31am
   
Your question is not clear! Please, be more specific and provide more details about your database structure and relationships between tables.
User_Michel 19-Mar-21 5:27am
   
Simply, i want every employee take a code depend on his department in company, like HR_001, HR_002... or Sls_001, Sls_002... all this in one column (emp_Code) from department abbreviation name (dept_ShortName) and add one number (+1) each new employee.

Basically, don't. While it's possible it's probably a poor idea simply because SQL is multiuser so in production unless you are really, really careful it's far too easy to generate duplicate values.

You can get it right by not trying to increment anything yourself.

Use an internal ID - IDENTITY is fine - to give every employee a sequential number. Link the employee to the department via a foreign key. Make the DepartmentID the "short sequence" so they don't get duplicated.

Then create a stored procedure to add your user.
INSERT the base data, then use a SELECT to find all the employees in the same department, and use ROW_NUMBER[^] to get the employee number in the department from the IDENTITY value you just created by INSERTing.
   
Quote:
Simply, i want every employee take a code depend on his department in company, like HR_001, HR_002... or Sls_001, Sls_002... all this in one column (emp_Code) from department abbreviation name (dept_ShortName) and add one number (+1) each new employee.


Well... It's not recommended due to tons of reason. Imagine, what happen when user is going to change Department? You'll be in need to change his ID or create another user with the same data and new ID. In the first case you'll loose relationships in other tables. In the second - it will produce duplicates!

Well, OriginalGriff already warned you about other possible issues and gave you instructions how to do it properly...
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900