SQL: Date ranges from multiple rows-CodePudding

I am trying to create date ranges from multiple rows. In the data below, I'd like to get the start and end dates for each time an employee switches department. I created a query, but unfortunately it doesn't give me the results I need when an employee moves from one department to another and later moves back to the original department.

    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;

The query gives the following results:

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

However, these are not the results I want for department codes 928 and 978. The results I'd like are:

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

I'm targeting SQL Server 2014. Any pointers?

CodePudding user response：

This is a sort of gaps and islands problem, you need to find the groups of continuous DepartmentCodes.

You can use row_number() to spot where the DepartmentCode changes and then group by this to identify the "gaps", then aggregate:

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;

See Demo Fiddle