在 SQL 中按连续日期周期对记录进行分组

问：

我有一张这样的表格：

而且我需要能够识别没有周期的连续位置，以及每个连续周期输出的最小开始和最大结束日期。预期结果如下所示：

我尝试了以下方法，但没有成功，我现在有点难住了。

WITH RankedPositions AS (
  SELECT
    [t104f005_employee_no],
    [t104f040_position_no],
    [t104f025_date_effective],
    [t104f030_date_to],
    ROW_NUMBER() OVER (PARTITION BY [t104f005_employee_no] ORDER BY [t104f025_date_effective]) -
    ROW_NUMBER() OVER (PARTITION BY [t104f005_employee_no], [t104f040_position_no] ORDER BY [t104f025_date_effective]) AS grp
  FROM [AUR11PROD].[dbo].[t104_employment_history] with (nolock)
  WHERE [t104f005_employee_no] = '11354'
)    
SELECT
  [t104f005_employee_no],
  [t104f040_position_no],
  MIN([t104f025_date_effective]) AS min_startdate,
  MAX([t104f030_date_to]) AS max_enddate
FROM RankedPositions
GROUP BY [t104f005_employee_no], [t104f040_position_no], grp
ORDER BY [t104f005_employee_no], min_startdate;

还有这个

WITH RECURSIVE ContinuousPositions AS (
  SELECT
    employee_no,
    position_no,
    startdate,
    enddate
  FROM your_table_name
  WHERE NOT EXISTS (
    SELECT 1
    FROM your_table_name AS t2
    WHERE t2.employee_no = your_table_name.employee_no
      AND t2.position_no = your_table_name.position_no
      AND t2.startdate < your_table_name.startdate
  )
  
  UNION ALL
  
  SELECT
    cp.employee_no,
    cp.position_no,
    cp.startdate,
    t.enddate
  FROM ContinuousPositions AS cp
  JOIN your_table_name AS t ON (
    cp.employee_no = t.employee_no
    AND cp.position_no = t.position_no
    AND cp.enddate = DATEADD(day, -1, t.startdate)
  )
)    
SELECT
  employee_no,
  position_no,
  MIN(startdate) AS min_startdate,
  MAX(enddate) AS max_enddate
FROM ContinuousPositions
GROUP BY employee_no, position_no
ORDER BY employee_no, min_startdate;