Calculate Logic Column ID

Question

I would like to offer suggestions for solving the following requirement.

I have a table with three fields: EmployeeId, HireDate and DepartmentId.

EmployeeId should be in the following format: yyyyddddxxxx where:

  yyyy - is the year the employee was hired.
  dddd - DepartmentId.
  xxxx - running number for each department on each year.

Would this be best computed using sql or C #? How to calculate the part "xxxx"? If necessary, I can add more fields to the table.

Answer 1

Magic!

;with Employees (EmployeeId, HireDate, DepartmentId) as
(
 select 1, getdate()-10, 1 union 
 select 2, getdate()-10, 1 union 
 select 3, getdate()-8, 2 union 
 select 4, getdate()-7, 3 union 
 select 5, getdate()-6, 1  
)
select cast(datepart(year, HireDate) as varchar(4)) + 
 right(replicate('0' ,4)+cast(DepartmentId as varchar(4)), 4) +
 right(replicate('0' ,4)+cast(row_number() over (partition by DepartmentId,datepart(year, HireDate) order by HireDate asc) as varchar(4)), 4) EmployeeCode
 ,DepartmentId
 ,EmployeeId 
 ,convert(varchar(10), HireDate, 120) HireDate
from Employees

Give us the following:

EmployeeCode DepartmentId EmployeeId  HireDate
------------ ------------ ----------- ----------
201000010001 1            1           2010-09-05
201000010002 1            2           2010-09-05
201000010003 1            5           2010-09-09
201000020001 2            3           2010-09-07
201000030001 3            4           2010-09-08

UPDATE:

Now suppose you want to add a new employee to Department No. 2 today. Here is how I would calculate the new EmployeeCode for this employee:

declare @DepartmentId int
set @DepartmentId = 2
select 
 cast(datepart(year, getdate()) as varchar(4)) + 
 right(replicate('0' ,4)+cast(@DepartmentId as varchar(4)), 4) +
 right(replicate('0' ,4)+cast(isnull(max(cast(right(EmployeeCode,4) as smallint)),0) + 1 as varchar(4)), 4) EmployeeCode
from dbo.Employees as e
where DepartmentId = @DepartmentId 
and datepart(year, hiredate) = datepart(year, getdate())

UPDATE:

, , , # 200, max null, , isnulled 0 + 1, 201002000001 .

, , 2011, max, , 201102000001 .

Answer 2

, employeeId ( PRIMARY KEY SQL Server).

, :

CREATE TABLE employee
        (
        employeeId AS
                RIGHT(REPLICATE('0', 4) + CAST(year AS VARCHAR), 4) +
                RIGHT(REPLICATE('0', 4) + CAST(dept AS VARCHAR), 4) +
                RIGHT(REPLICATE('0', 4) + CAST(id AS VARCHAR), 4) PERSISTED NOT NULL PRIMARY KEY,
        id INT NOT NULL,
        dept INT NOT NULL,
        year INT NOT NULL,
        CHECK (id BETWEEN 0 AND 9999),
        CHECK (dept BETWEEN 0 AND 9999),
        CHECK (year BETWEEN 0 AND 9999)
        )        
GO
CREATE PROCEDURE prcAddEmployee(@dept INT, @year INT, @employeeId VARCHAR OUT)
AS
        DECLARE @tt TABLE (employeeId VARCHAR(12))
        INSERT
        INTO    employee (id, dept, year)
        OUTPUT  INSERTED.employeeId
        INTO    @tt
        VALUES  (
                (
                SELECT  COALESCE(MAX(id), 0) + 1
                FROM    employee WITH (TABLOCK)
                WHERE   dept = @dept
                        AND year = @year
                ),
                @dept, @year
                )
        SELECT  @employeeId = employeeId
        FROM    @tt
GO        

:

DECLARE @employeeId VARCHAR(12)
EXEC prcAddEmployee 1, 2010, @employeeId
EXEC prcAddEmployee 1, 2010, @employeeId
EXEC prcAddEmployee 2, 2010, @employeeId
EXEC prcAddEmployee 2, 2010, @employeeId
EXEC prcAddEmployee 1, 2010, @employeeId

SELECT  *
FROM    employee

Answer 3

xxxx 4 , , ( 100 ). , .

The best approach is to create a separate table containing the last number and year.

Be very careful when updating this table. Check if the other client updated the number between when you checked it out and want to update it. Use the syntax as follows:

UPDATE xxxxTable
SET number = newNumber
WHERE year = @year and number = @oldnumber

Then check the number of rows updated, if it is not 1, you must create a new number.