SQL query to get active employees for a certain 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 to get the number of active (or partially active) for a certain 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 only 3 (because employee 110454 has reconciled).

How do i do this?

Answer 1

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

Useful indices:

 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:

Sqlfiddle here

Answer 2

1. Turn 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. Acquire active employees based on this condition:

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

3. Consider Excellent EmployeeID Values.

Here's 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' ; 

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

Answer 3

Note. I added a demo.

Each [Status] converted to in = +1 or out = -1 (see the StatusSign column). This decision checks if the employee was active at the beginning or at the end of the requested time period:

 DECLARE @StartDate DATE, @EndDate DATE; SELECT @StartDate='2012-08-01', @EndDate='2013-01-01'; SELECT * FROM( SELECT x.EmployeeID, SUM(CASE WHEN x.EffectiveDate< =@StartDate THEN x.StatusSign ELSE 0 END) AS StartDate_Status, SUM(CASE WHEN x.EffectiveDate BETWEEN @StartDate AND @EndDate AND x.StatusSign=+1 THEN 1 ELSE 0 END) AS IntermediateDate_Status, SUM(CASE WHEN x.EffectiveDate< =@EndDate THEN x.StatusSign ELSE 0 END) AS EndDate_Status FROM ( SELECT h.EmployeeID, h.EffectiveDate, CASE WHEN h.[Status]='Active' THEN +1 WHEN h.[Status]='Resigned' THEN -1 ELSE 1/0 -- Internal error: unknown [Status] END StatusSign FROM @EmployeeHistory h WHERE h.EffectiveDate< =@EndDate )x GROUP BY x.EmployeeID )y WHERE y.StartDate_Status=1 OR y.IntermediateDate_Status=1 OR y.EndDate_Status=1; --WHERE y.StartDate_Status>=1 OR y.IntermediateDate_Status>=1 OR y.EndDate_Status>=1; 

Answer 4

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 SQLFiddle

Answer 5

This should work (not verified)

 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' 

Answer 6

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

Answer 7

You can use this query to create a list of employees and start / resign 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 point here is the use of OUTER APPLY , which allows the use of a rather "funky" connection criterion.

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

---

From here it’s just a matter of querying records whose busy interval spans the target interval.

There are many ways to write this, but I personally like to use CTE because I find it 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