SQL query for getting active employees in specific period

Question

Having the following table:

    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

How do I get the number of active (or partially active) in a specific period? For example, if I want to know all active (or partially active) employees from 01JAN2011 to 01AUG2012 I should get 4 (according to the table above). If I want to know all active employees from 01AUG2012 to 01JAN2013 it should be 3 only (because employee 110454 is resigned).

How will I do that?

is it possible for an employee to have worked two different periods? — Cristian Lupascu, Jul 26 '13 at 06:51
yes. two different periods but ofcourse they do not overlap... that would be against physics laws — , Jul 26 '13 at 06:51
So, it is assumed that an employee is active from the given effective date until the day before the subsequent Resigned entry for the same employee, and vice versa (for when they're re-hired) — Damien_The_Unbeliever, Jul 26 '13 at 06:59

score 8 · Accepted Answer · 2013-07-26T09:11:19.037

Sample data:

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');

Helpful indexes:

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

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

Solution with comments in-line:

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;

Execution plan:

Execution plan

SQLFiddle here

What tool are you using to have a graphical representation of the Execution plan? Super useful. Can you share please? — bonCodigo, Aug 01 '14 at 10:06

score 4 · Answer 2 · answered Jul 26 '13 at 07:59

1. Turn your events into ranges:

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

2. Get active employees based on this condition:

WHERE Status = 'Active'
  AND StartDate < @EndDate
  AND EndDate > @StartDate

3. Count distinct EmployeeID values.

This is how you could implement the above:

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EffectiveDate)
  FROM EmployeeActivity
),
ranges AS (
  SELECT
    s.EmployeeID,
    s.Status,
    StartDate = s.EffectiveDate,
    EndDate   = ISNULL(e.EffectiveDate, '31DEC9999')
  FROM ranked s
  LEFT JOIN ranked e ON s.EmployeeID = e.EmployeeID AND s.rn = e.rn - 1
)
SELECT
  ActiveCount = COUNT(DISTINCT EmployeeID)
FROM ranges
WHERE Status = 'Active'
  AND StartDate < '01JAN2013'
  AND EndDate   > '01AUG2012'
;

A SQL Fiddle demo for this query: http://sqlfiddle.com/#!3/c3716/3

score 1 · Answer 3 · answered Jul 26 '13 at 06:58

1

This should work (not tested)

SELECT COUNT DISTINCT EmployeeID FROM TABLE 
WHERE EffectiveDate > CONVERT(VARCHAR(11), '08-01-2012', 106) AS [DDMONYYYY] 
and EffectiveDate < CONVERT(VARCHAR(11), '01-01-2013', 106) AS [DDMONYYYY]
AND Status = 'Active'

answered Jul 26 '13 at 06:58

Hassan

1,413
1
10
12

I would use 'EffectiveDate between and ', but that's just preference. This seems to be the most simple solution. – Sam Jul 26 '13 at 07:01
4

This is so wrong :) if the employee is active from `01JAN2011` up to now, and the query period is from `01jan2012` to `01jul2012` the employee will not show... – Jul 26 '13 at 07:09

Aleksandr Fedorenko · Answer 4 · 2013-07-26T15:43:45.180

1

Another solution using the PIVOT operator

DECLARE @StartDate date = '20120801',
        @EndDate date = '20130101'
SELECT COUNT(*)
FROM (
      SELECT EffectiveDate, EmployeeID, [Status]
      FROM EmployeeActivity
      WHERE EffectiveDate < @EndDate
      ) x
PIVOT
 (
  MAX(EffectiveDate) FOR [Status] IN([Resigned], [Active])
  ) p
WHERE ISNULL(Resigned, '99991231') > @StartDate

See demo on SQLFiddle

edited Jul 26 '13 at 15:43

answered Jul 26 '13 at 15:37

Aleksandr Fedorenko

16,594
6
37
44

thanks @Paul. If I understand correctly, OP want all active employee from the given effective date until the day before subsequent Resigned entry.In this case, the result should be 0 – Aleksandr Fedorenko Jul 29 '13 at 07:52

Nenad Zivkovic · Answer 5 · 2013-07-26T07:23:16.120

This should work fine:

DECLARE @d1 date = '01AUG2012';
DECLARE @d2 date = '01JAN2014';

WITH CTE_Before AS 
(
    --Last status of each employee before period will be RN=1
    SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EffectiveDate DESC) RN
    FROM dbo.Table1
    WHERE EffectiveDate < @d1
)
, CTE_During AS
(
    --Those who become active during period
    SELECT * FROM dbo.Table1
    WHERE [Status] = 'Active' AND EffectiveDate BETWEEN @d1 AND @d2
)
--Union of those who were active at the beginning of period and those who became active during period
SELECT EmployeeID FROM CTE_Before WHERE RN = 1 AND Status = 'Active'
UNION
SELECT EmployeeID FROM CTE_During

SQLFiddle DEMO

Cristian Lupascu · Answer 6 · 2013-07-26T08:22:16.363

You can use this query to build a list of employees and their start/resignation dates:

select 
  start.*,
  resignation.EffectiveDate as ResignationDate
from Employment start
outer apply (
  select top 1 
    Id,
    EmployeeId,
    EffectiveDate
  from Employment
  where EmployeeId = start.EmployeeId
  and Status = 'Resigned'
  and Id > start.Id
  order by Id  
) resignation
where start.Status='Active'

The key here is the use of OUTER APPLY, which allows us to use a pretty "funky" join criterion.

Here's how it works: http://www.sqlfiddle.com/#!3/ec969/7

From here, it's just a matter of querying the records whose the employment interval overlaps the target interval.

There are many ways to write this, but I personally like using a CTE, because I find it a bit more readable:

;with EmploymentPeriods as (
    select 
      start.EmployeeId,
      start.EffectiveDate as StartDate,
      isnull(resignation.EffectiveDate, '9999-01-01') as EndDate 
    from Employment start
    outer apply (
      select top 1 
        Id,
        EmployeeId,
        EffectiveDate
      from Employment
      where EmployeeId = start.EmployeeId
      and Status = 'Resigned'
      and Id > start.Id
      order by Id  
    ) resignation
    where start.Status='Active'
)
select distinct EmployeeId
from EmploymentPeriods
where EndDate >= @QueryStartDate
  and StartDate <= @QueryEndDate

SQLFiddles:

first interval: http://www.sqlfiddle.com/#!3/ec969/27
second interval: http://www.sqlfiddle.com/#!3/ec969/26

Test for overlapping ranges `(StartDate, EndDate)` and `(QueryStartDate, QueryEndDate)` [can be much simpler as per this SO answer](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap). — Nikola Markovinović, Jul 26 '13 at 08:16

SQL query for getting active employees in specific period

6 Answers6