I have a table like this:

我有一张这样的桌子：

And I need to be able to identify the continuous position no periods with the min start and max end date outputted for each continuous period. Expected outcome would look like this:

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

I have tried the following methods but it's not working out and I'm a bit stumped now.

我尝试了以下方法，但都不起作用，现在我有点困惑了。

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;

and this one

还有这个

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;

This is a gaps and islands problem. One way is to use LEAD() or LAG() to identify when the continuous date break and set a flag (g). Performing a cumulative sum() on the flag gives you the required grouping (grp).

这是一个缺口和岛屿问题。一种方法是使用Lead()或Lag()来标识连续日期何时中断，并设置一个标志(G)。对标志执行累积求和()将得到所需的分组(GRP)。

with 
cte as
(
    select employee_no, position_no, date_effective, date_expiry,
           g = case when dateadd(day, -1, date_effective)
                    <>   lag(date_expiry) over (partition by employee_no, position_no
                                                    order by date_effective)
                    then 1
                    else 0
                    end
    from   employment_history
),
cte2 as
(
   select employee_no, position_no, date_effective, 
          date_expiry = isnull(date_expiry, '99991231'), 
          grp = sum(g) over (partition by employee_no, position_no
                                 order by date_effective)  
    from  cte
)
select employee_no, position_no, 
       date_effective = min(date_effective), 
       date_expiry    = nullif(max(date_expiry), '99991231')
from   cte2
group by employee_no, position_no, grp
order by employee_no, date_effective

db<>fiddle demo

DB<>小提琴演示