Calculate column id with logic

Question

Hi,

I would like suggestions how to solve the following demand.

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

The EmployeeId needs to 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 do I calculate the 'xxxx' part? I can add more fields to the table if needed.

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

Will 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 supposing you want to add a new employee to the Department #2 today. Here's how I'd calculate a 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:

As you can see, if you add an employee to a hitherto nonexistent department, say, #200, then the max clause would return null as there are no employees in this department and it'd get isnulled to 0 + 1, so you'd get a perfectly normal 201002000001 for that employee.

Suppose a year passed and now it's 2011 and this last filtering clause again would null the max clause and the procedure would repeat and we'd get 201102000001 for a new employee in that new department this next-year.

Answer 2

Since xxxx only contains 4 numbers, I don't think this function is heavily used (I mean 100 times or more per second). So I do not think you have to worry about locking and performance too much.

The best approach would be to create a separate table, that contains the latest number given and the year.

Be very careful when updating this table. Check if another client updated the number between when you retrieved it, and want to update it. Use syntax like this:

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

Then check for the number of rows updated, if this is not 1, you should create a new number.

Answer 3

To make your queries easier, you can make the employeeId a computed column (which can as well be a PRIMARY KEY in SQL Server).

I'd suggest you to write a stored procedure to add your employees:

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        

Here's a code to check:

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