将事务配对到日期范围行中
将事务配对到日期范围行中
提问于 2018-12-19 23:47:26
我有一个结构类似于下面的表，该表显示了何时将员工作为特定角色添加(operation=I)或从帐户中删除(operation=D
Account | Employee | Role | Operation | OperationTimestamp
ABC | 1 | Rep | I | 1/1/2018
DEF | 1 | Mgr | I | 1/1/2018
ABC | 1 | Rep | D | 3/31/2018
ABC | 1 | Rep | I | 7/1/2018
ABC | 1 | Rep | D | 12/31/2018
ABC | 2 | Mgr | I | 1/1/2018
DEF | 2 | Exc | I | 1/1/2018
ABC | 2 | Mgr | D | 3/31/2018
ABC | 2 | Mgr | I | 6/1/2018
ABC | 2 | Mgr | D | 10/31/2018(I =插入，D=删除)
我需要开发一个查询，它将返回该员工在该帐户上的帐户、员工、角色和日期范围，如下所示：
Account | Employee | Role | StartingDate | EndingDate
ABC | 1 | Rep | 1/1/2018 | 3/31/2018
DEF | 1 | Mgr | 1/1/2018 | NULL
ABC | 1 | Rep | 7/1/2018 | 12/31/2018
ABC | 2 | Mgr | 1/1/2018 | 3/31/2018
DEF | 2 | Exc | 1/1/2018 | NULL
ABC | 2 | Mgr | 6/1/2018 | 10/31/2018因此，正如您从结果集中看到的那样，如果一个员工被添加到一个帐户，但没有被删除，那么EndingDate应该为NULL。
我正在努力的地方是，您可以多次在帐户中添加/删除同一员工和/或在多个角色中添加/删除相同的员工。我的直觉告诉我，我需要按account>employee>role>date对事务进行排序，并以某种方式将每2行组合在一起(因为它应该始终是一个I操作，后面跟着一个D操作)，但我不确定如何处理“丢失”的删除(如果它们仍然在一个帐户上)。
Stack Overflow用户
发布于 2018-12-20 00:01:54
对于row_number和self join，这是相当简单的：
declare @t table(Account varchar(3), Employee int, EmpRole varchar(3), Operation varchar(1), OperationTimestamp datetime);
insert into @t values
('ABC',1,'Rep','I','20180101')
,('DEF',1,'Mgr','I','20180101')
,('ABC',1,'Rep','D','20180331')
,('ABC',1,'Rep','I','20180701')
,('ABC',1,'Rep','D','20181231')
,('ABC',2,'Mgr','I','20180101')
,('DEF',2,'Exc','I','20180101')
,('ABC',2,'Mgr','D','20180331')
,('ABC',2,'Mgr','I','20180601')
,('ABC',2,'Mgr','D','20181031');
with d as
(
select Account
,Employee
,EmpRole
,Operation
,OperationTimestamp
,row_number() over (partition by Account, Employee, EmpRole order by OperationTimestamp) as ord
from @t
)
select s.Account
,s.Employee
,s.EmpRole
,s.OperationTimestamp as OperationTimestampStart
,e.OperationTimestamp as OperationTimestampEnd
from d as s
left join d as e
on s.Account = e.Account
and s.Employee = e.Employee
and s.EmpRole = e.EmpRole
and s.ord = e.ord-1
where s.Operation = 'I';输出
+---------+----------+---------+-------------------------+-----------------------+
| Account | Employee | EmpRole | OperationTimestampStart | OperationTimestampEnd |
+---------+----------+---------+-------------------------+-----------------------+
| ABC | 1 | Rep | 2018-01-01 | 2018-03-31 |
| ABC | 1 | Rep | 2018-07-01 | 2018-12-31 |
| ABC | 2 | Mgr | 2018-01-01 | 2018-03-31 |
| ABC | 2 | Mgr | 2018-06-01 | 2018-10-31 |
| DEF | 1 | Mgr | 2018-01-01 | NULL |
| DEF | 2 | Exc | 2018-01-01 | NULL |
+---------+----------+---------+-------------------------+-----------------------+页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/53854717
复制相关文章
相似问题
腾讯云开发者
