sql - SQL查询以获取特定时期的在职员工

有下表：

    ID     EmployeeID      Status       EffectiveDate
  ------------------------------------------------------
     1       110545        Active        01AUG2011
     2       110700        Active        05JAN2012
     3       110060        Active        05JAN2012
     4       110222        Active        30JUN2012
     5       110545        Resigned      01JUL2012
     6       110545        Active        12FEB2013

如何获得特定期间的有效（或部分有效）数量？
例如，如果我想知道从01JAN2011到01AUG2012的所有在职（或部分在职）雇员，我应该得到4（根据上表）。如果我想知道所有从01AUG2012到01JAN2013的在职员工，则该人数应仅为3（因为员工110454辞职了）。

我该怎么做？

最佳答案

样本数据：

CREATE TABLE #Employee
(
    ID              integer NOT NULL,
    EmployeeID      integer NOT NULL,
    [Status]        varchar(8) NOT NULL,
    EffectiveDate   date NOT NULL,

    CONSTRAINT [PK #Employee ID]
        PRIMARY KEY CLUSTERED (ID)
);

INSERT #Employee
    (ID, EmployeeID, [Status], EffectiveDate)
VALUES
     (1, 110545, 'Active', '20110801'),
     (2, 110700, 'Active', '20120105'),
     (3, 110060, 'Active', '20120105'),
     (4, 110222, 'Active', '20120630'),
     (5, 110545, 'Resigned', '20120701'),
     (6, 110545, 'Active', '20130212');

有用的索引：

CREATE NONCLUSTERED INDEX Active
ON #Employee
    (EffectiveDate)
INCLUDE
    (EmployeeID)
WHERE
    [Status] = 'Active';

CREATE NONCLUSTERED INDEX Resigned
ON #Employee
    (EmployeeID, EffectiveDate)
WHERE
    [Status] = 'Resigned';

内嵌评论的解决方案：

CREATE TABLE #Selected (EmployeeID integer NOT NULL);

DECLARE
    @start date = '20110101',
    @end   date = '20120801';

INSERT #Selected (EmployeeID)
SELECT
    E.EmployeeID
FROM #Employee AS E
WHERE
    -- Employees active before the end of the range
    E.[Status] = 'Active'
    AND E.EffectiveDate <= @end
    AND NOT EXISTS
    (
        SELECT *
        FROM #Employee AS E2
        WHERE
            -- No record of the employee
            -- resigning before the start of the range
            -- and after the active date
            E2.EmployeeID = E.EmployeeID
            AND E2.[Status] = 'Resigned'
            AND E2.EffectiveDate >= E.EffectiveDate
            AND E2.EffectiveDate <= @start
    )
OPTION (RECOMPILE);

-- Return a distinct list of employees
SELECT DISTINCT
    S.EmployeeID
FROM #Selected AS S;

执行计划：



SQLFiddle here