SQL Server：同一表中的重叠日期

Question

TableA (
    id int, 
    match1 char,
    match2 char,
    startdate datetime,
    enddate,
    status char
)

示例数据：

 id match1  match2   startdate   enddate        Status
 1  AAA     BBB      2006-01-01  2007-01-01 
 2  AAA     BBB      2006-12-12  2008-01-01
 3  AAA     BBB      2008-01-01  2012-02-02
 4  AAA     BBB      2002-01-01  2004-01-01
 5  DDD     EEE      2009-01-01  2012-01-01
 6  DDD     EEE      2011-01-01  2020-01-01
 7  DDD     EEE      2013-01-01  2015-01-01
 8  DDD     EFG      2009-01-01  2012-01-01

当匹配 match1, match2 时，我必须在 tableA 中用 'FAIL' 填充 status，日期 - 开始和结束 - 彼此重叠。

当 ID = 2 时，生效日期为 2006-12-12 至 2008-01-01，与 ID = 1< /代码>。因此，ID = 2 得到'FAIL'。 ID = 6 也是如此。

预期结果：

 id match1  match2    startdate    enddate        Status
 1  AAA     BBB      2006-01-01  2007-01-01       NULL
 2  AAA     BBB      2006-12-12  2008-01-01       FAIL
 3  AAA     BBB      2008-01-01  2012-02-02       NULL
 4  AAA     BBB      2002-01-01  2004-01-01       NULL    
 5  DDD     EEE      2009-01-01  2012-01-01       NULL
 6  DDD     EEE      2011-01-01  2020-01-01       FAIL
 7  DDD     EEE      2013-01-01  2015-01-01       NULL
 8  DDD     EFG      2009-01-01  2012-01-01       NULL (because It has different match2)

此外，我们保留相同 match1 和 match2 的第一个记录，并且使新传入的重叠记录失败。

Answer 1

为什么 ID 7 不是失败？它与 ID 6 重叠（相交）？

<块引用>

ID7 并未失败，因为 ID6 已失败并已从我们的考虑范围中排除

您在注释中提出的这一点消除了仅 SQL 解决方案的可能性，因为您对 FAIL 的定义变得递归。换句话说，为了知道某件事是否失败，仅仅知道开始和结束日期是不够的：您还必须知道通过或失败状态的“上一代”记录。

下面是一个查询，可以帮助您确定第一个重叠 ID，忽略“与失败记录的重叠不计入”规则：

select a.*,(
    select top 1 b.id from tableA b
    where a.match1=b.match1 and a.match2=b.match2
    and a.startdate>b.startdate
    and (case when a.startdate<b.startdate then b.startdate else a.startdate end) <
    (case when a.enddate>b.enddate then b.enddate else a.enddate end)
    order by b.startdate asc
) as OverlappingId
from tableA a

它返回表中数据的以下结果：

1   AAA BBB 2006-01-01  2007-01-01  NULL
2   AAA BBB 2006-12-12  2008-01-01  1
3   AAA BBB 2008-01-01  2012-02-02  NULL
4   AAA BBB 2002-01-01  2004-01-01  NULL
5   DDD EEE 2009-01-01  2012-01-01  NULL
6   DDD EEE 2011-01-01  2020-01-01  5
7   DDD EEE 2013-01-01  2015-01-01  6
8   DDD EFG 2009-01-01  2012-01-01  NULL

如果您必须注意“与失败的重叠”规则，需要依次应用； SQL 不太擅长。

Answer 2

为此，您需要一个递归 CTE 或游标。下面是递归 CTE 方法。

;WITH BaseData(id,match1,match2,startdate,enddate)
     AS (SELECT 1,'AAA','BBB','2006-01-01','2007-01-01' UNION ALL
         SELECT 2,'AAA','BBB','2006-12-12','2008-01-01' UNION ALL
         SELECT 3,'AAA','BBB','2008-01-01','2012-02-02' UNION ALL
         SELECT 4,'AAA','BBB','2002-01-01','2004-01-01' UNION ALL
         SELECT 5,'DDD','EEE','2009-01-01','2012-01-01' UNION ALL
         SELECT 6,'DDD','EEE','2011-01-01','2020-01-01' UNION ALL
         SELECT 7,'DDD','EEE','2013-01-01','2015-01-01' UNION ALL
         SELECT 8,'DDD','EFG','2009-01-01','2012-01-01'    ),
     RecursiveCTE
     AS (SELECT id,
                match1,
                match2,
                startdate,
                enddate,
                CAST(NULL AS VARCHAR(4)) AS Status,
                enddate                  AS LastSuccessfulEnd
         FROM   (SELECT *,
                        ROW_NUMBER() OVER (PARTITION BY match1, match2 
                                               ORDER BY startdate) RN
                 FROM   BaseData) B
         WHERE  RN = 1
         UNION ALL
         SELECT id,
                match1,
                match2,
                startdate,
                enddate,
                Status,
                LastSuccessfulEnd
         FROM   (
                SELECT B.*,
                       rn = ROW_NUMBER() OVER (PARTITION BY B.match1, B.match2 
                                                   ORDER BY B.startdate),
                       CASE
                         WHEN B.startdate < R.LastSuccessfulEnd THEN 'FAIL'
                       END AS Status,
                       CASE
                         WHEN B.startdate >= R.LastSuccessfulEnd THEN B.enddate
                         ELSE R.enddate
                       END AS LastSuccessfulEnd
                 FROM   BaseData B
                        JOIN RecursiveCTE R
                          ON R.match1 = B.match1
                             AND R.match2 = B.match2
                             AND B.startdate > R.startdate) R
         WHERE  R.rn = 1)
SELECT id,
       match1,
       match2,
       startdate,
       enddate,
       Status
FROM   RecursiveCTE
ORDER  BY id