您可以尝试的一个可能的解决方案是使用 lag。
首先获得当前PolicyEnd和下一个PolicyStart之间的间隔月数，然后可以找到间隔大于1个月的每个客户的最新记录。
然后，您可以选择此点 * 之后 * 每个客户的最小有效记录。

with gaps as (
  select *,
    case when DateDiff(month, PolicyEnd,Lead(PolicyStart) over(partition by CustomerID order by Record)) > 1 then 1 else 0 end b
  from t
), minrecord as (
  select CustomerID, Max(Record) mr
  from gaps
  where b = 1
  group by CustomerID
)
select Record, CustomerID, [Name], Agent, [Status], ed.OriginialEffectiveDate
from t
cross apply (
  select Min(PolicyStart) OriginialEffectiveDate
  from t t2
  where t2.CustomerID = t.CustomerID and
    t2.Record > IsNull((select mr from minrecord where CustomerID = t2.CustomerID), 0)
)ed
where t.[Status] = 'active';

查看此demo Fiddle

以下是我最终的结论：

SELECT Record
    ,Customers.CustomerID
    ,Name
    ,CustomerEffectiveDate
    ,Agent
    ,Status
FROM Customers
JOIN (
    SELECT CustomerID
        ,MIN(PolicyStart) AS CustomerEffectiveDate
    FROM (
        SELECT CustomerID
            ,PolicyStart
            ,DateDiff(month, PolicyEnd, Lead(PolicyStart) OVER (
                    PARTITION BY CustomerID ORDER BY CustomerID
                        ,PolicyStart
                    )) AS gapMonths
        FROM Customers
        ) gaps
    WHERE gapMonths < 2
        OR gapMonths IS NULL
    GROUP BY CustomerID
    ) minstart ON customers.CustomerID = minstart.CustomerID
WHERE status = 'Active'

这可能不是最有效的代码，但似乎可以工作。