Question

我想就如何满足以下需求提出建议。

我的表格分为三个领域: Id, HireDate and Department 同上。

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

将给我们如下:

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:

现在,你想在该部增加一名新雇员。这里,我是如何计算该雇员的新雇员:

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号,那么该最高条款将告无效,因为该部门没有雇员,而且该规定被推到0+1,因此,该雇员的“<代码>201002000001<>>>完全正常。

一年过去了,现在又是2011年,最后一项过滤条款将再次取消最高条款,而今后一年,我们将为新部门的一名新雇员提供<>201102000001。

Answer 2

为方便查询,您可制作<条码>。页: 1 缩略语服务器。

我建议你写一份储存程序,增加雇员:

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次或100多次)。因此,我认为你不必担心锁定和业绩太多。

最好的办法是建立一个单独的表格,其中载列最新数字和年份。

在增订本表时非常谨慎。如果另一个客户在查询时更新数字,希望更新。类似:

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

然后,如果情况不是1,那么对新行数目的检查就会增加。

友情链接