我正在嘗試從多行創建日期范圍。在下面的資料中，我想獲取員工每次切換部門的開始和結束日期。我創建了一個查詢，但不幸的是，當員工從一個部門轉移到另一個部門，然后又回到原來的部門時，它沒有給我所需的結果。
declare @Audits table
(
EmployeeId int,
DepartmentCode varchar(10),
ActionTaken varchar(50),
ActionDate date
)
insert into @Audits values (1, '978', 'Update Name', '2022-1-1')
insert into @Audits values (1, '978', 'Update Salary', '2022-2-1')
insert into @Audits values (1, '928', 'Update Department', '2022-3-1')
insert into @Audits values (1, '978', 'Update Role', '2022-4-1')
insert into @Audits values (1, '978', 'Update Job', '2022-5-1')
insert into @Audits values (1, '911', 'Update Department', '2022-6-1')
insert into @Audits values (1, '911', 'Update Salary', '2022-7-1')
insert into @Audits values (1, '911', 'Update Job', '2022-8-1')
select
EmployeeId,
DepartmentCode,
ActionDate as StartDate,
EndDate = isnull(lead(ActionDate, 1) over (partition by EmployeeId order by ActionDate), '9999-12-31')
from
(
select EmployeeId, DepartmentCode, min(ActionDate) as ActionDate
from @Audits
group by EmployeeId, DepartmentCode
) d
order by EmployeeId, ActionDate;
該查詢給出以下結果：
EmployeeId DepartmentCode StartDate EndDate
----------- -------------- ---------- ----------
1 978 2022-01-01 2022-03-01
1 928 2022-03-01 2022-06-01
1 911 2022-06-01 9999-12-31
但是，這些不是我想要的部門代碼 928 和 978 的結果。我想要的結果是：
EmployeeId DepartmentCode StartDate EndDate
----------- -------------- ---------- ----------
1 978 2022-01-01 2022-03-01
1 928 2022-03-01 2022-04-01
1 978 2022-04-01 2022-06-01
1 911 2022-06-01 9999-12-31
我的目標是 SQL Server 2014。任何指標？
uj5u.com熱心網友回復：
這是一種間隙和孤島問題，您需要找到連續的部門代碼組。
您可以使用row_number()發現 DepartmentCode 更改的位置，然后按此分組以識別“差距”，然后聚合：
with g as (
select *, Row_Number() over(partition by employeeId order by ActionDate)
- Row_Number() over(partition by employeeId, DepartmentCode order by ActionDate) gp
from audits
), d as (
select employeeId, DepartmentCode, Min(ActionDate) StartDate
from g
group by employeeId, DepartmentCode, gp
)
select *, lead(StartDate, 1, '99991231') over(partition by employeeId order by StartDate)
from d
order by Startdate;
見演示小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/491565.html
上一篇：如何復制同一列的前一行值？
下一篇：從跨列比較中排除空值