用于获取员工活跃到不活跃之间的转换天数的 SQL 查询

Question

ID EmployeeID 状态 EffectiveDate

---

 1       110545        Active        01-01-2011
 2       110700        Active        05-01-2012
 3       110060        Active        05-01-2012
 4       110222        Active        30-06-2012
 5       110222        Resigned      22-05-2016
 6       110545        Resigned      01-07-2012
 7       110545        Active        12-02-2013

如何使用 T-SQL 查找每个员工的状态为“事件”和“非事件”之间耗时量，不包括重新加入员工的当前状态为“事件”。

输出应该

ID     EmployeeID      Days

---

 1       110222     1422
 2       110545      371

Answer 1

一种方法是搜索所有Resigned 状态，然后使用cross apply 查找之前的Active 状态，如下所示:

declare @Emp table (ID int, EmployeeID int, Status varchar(8), EffectiveDate date)
insert into @Emp (ID, EmployeeID, Status, EffectiveDate) values
(1, 110545, 'Active', '2011-01-01'),
(2, 110700, 'Active', '2012-01-05'),
(3, 110060, 'Active', '2012-01-05'),
(4, 110222, 'Active', '2012-06-30'),
(5, 110222, 'Resigned', '2016-05-22'),
(6, 110545, 'Resigned', '2012-07-01'),
(7, 110545, 'Active', '2013-02-12')


select
    row_number() over (order by EmployeeID) as ID,
    e.EmployeeID,
    datediff(dd, e2.EffectiveDate, e.EffectiveDate) as Days
from @Emp as e
cross apply
(
    select top 1 e2.EffectiveDate
    from @Emp as e2
    where e.EmployeeID = e2.EmployeeID and e2.EffectiveDate < e.EffectiveDate
    order by EffectiveDate desc
) as e2
where e.Status = 'Resigned'

结果

ID     EmployeeID      Days
 1       110222       1422
 2       110545        547*

*您的示例 EffectiveDate 数据格式为 DD-MM-YYYY