日期重叠情景 [英] Dates overlapping scenario

问题描述

TableA（id int，match1 char，match2 char，match3 char，startdate datetime，enddate，status char）

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

id match1  match2  match3  startdate   enddate
1  AAA     BBB     CCC     2006-01-01  2007-01-01 
2  AAA     BBB     CCC     2006-12-12  2008-01-01
3  AAA     BBB     CCC     2008-01-01  2012-02-02

4  DDD     EEE     FFF     2009-01-01  2012-01-01
5  DDD     EEE     FFF     2013-01-01  2020-01-01

Ref TableB（match1 char，match2，match3，startdate datetime，enddate）

Ref TableB (match1 char, match2, match3, startdate datetime, enddate)

match1  match2 match3  startdate   enddate
AAA     BBB    CCC     2006-01-01  2015-01-01
DDD     EEE    FFF     2009-01-01  2015-01-01

好的，[Stat]为NULL，我必须用' FAIL'in tableA when：

Okay so [Stat] is NULL, I have to populate stat with 'FAIL' in tableA when:

1）为了匹配tableB中的match1，match2，match3，日期（开始和结束）存在于范围之外。让我们看到ID = 5的效果= 2013 - 2020年，但在其参考表中，其2009-2015年ID = 5得到失败。

1) For matching match1,match2,match3 in tableB, the dates(start and end) exists outside the scope. Lets see ID=5 effectivedates = 2013 - 2020 but in its ref table, its 2009-2015 so ID = 5 gets the 'fail'.

2） ，设置为match1，match2，match3等于记录，因此ID 123是一组，ID 4& 5是另一组。所以，在一组，有效的日期（开始和结束）日期重叠。即使在参考表中的相应记录的日期范围内存在1（ID1,2,3），但ID = 2的生效日期与ID = 1的生效日期重叠，因此ID = 2得到FAIL

2) In a set, set is records when match1, match2, match3 are equal so ID 123 is one set and ID 4&5 is another set. So, In a set, effective dates (start and end) dates overlap. even though set 1 (ID1,2,3) exists within the date range of its corresponding record in the reference table, but effective dates of ID = 2 overlaps the effective dates of ID = 1, so ID=2 gets the 'FAIL'

预期结果：

id match1  match2  match3  startdate   enddate     stat
1  AAA     BBB     CCC     2006-01-01  2007-01-01  NULL
2  AAA     BBB     CCC     2006-12-12  2008-01-01  FAIL
3  AAA     BBB     CCC     2008-01-01  2012-02-02  NULL
4  DDD     EEE     FFF     2009-01-01  2012-01-01  NULL
5  DDD     EEE     FFF     2013-01-01  2020-01-01  FAIL

感谢Advance。

推荐答案

p> SQL语句

;WITH CandidateIDs AS (
    SELECT  idOriginal = YourTable.id, idFailCandidate = ed.id
    FROM    YourTable
            INNER JOIN YourTable ed ON ed.match1 = YourTable.match1
                               AND ed.match2 = YourTable.match2
                               AND ed.startdate BETWEEN YourTable.startdate AND YourTable.enddate
                               AND ed.id <> YourTable.id
)                              
SELECT  *
FROM    YourTable
        INNER JOIN (
            SELECT  idFailCandidate
            FROM    CandidateIDs r1
            WHERE   NOT EXISTS (SELECT * FROM CandidateIDs WHERE CandidateIDs.idFailCandidate = r1.idOriginal)
        ) fail ON fail.idFailCandidate = YourTable.id           

测试脚本

;WITH YourTable (id, match1, match2, startdate, enddate, status) AS (
    SELECT 1, 'AAA', 'BBB', CAST('2006-01-01' AS DATETIME), CAST('2007-01-01' AS DATETIME), NULL
    UNION ALL SELECT 2, 'AAA', 'BBB', '2006-12-12', '2008-01-01', NULL
    UNION ALL SELECT 3, 'AAA', 'BBB', '2008-01-01', '2012-02-01', NULL
    UNION ALL SELECT 4, 'AAA', 'BBB', '2002-01-01', '2004-01-01', NULL
    UNION ALL SELECT 5, 'DDD', 'EEE', '2009-01-01', '2012-01-01', NULL
    UNION ALL SELECT 6, 'DDD', 'EEE', '2011-01-01', '2020-01-01', NULL
    UNION ALL SELECT 7, 'DDD', 'EEE', '2013-01-01', '2015-01-01', NULL
    UNION ALL SELECT 8, 'DDD', 'EFG', '2009-01-01', '2012-01-01', NULL
)
, CandidateIDs AS (
    SELECT  idOriginal = YourTable.id, idFailCandidate = ed.id
    FROM    YourTable
            INNER JOIN YourTable ed ON ed.match1 = YourTable.match1
                               AND ed.match2 = YourTable.match2
                               AND ed.startdate BETWEEN YourTable.startdate AND YourTable.enddate
                               AND ed.id <> YourTable.id
)                              
SELECT  *
FROM    YourTable
        INNER JOIN (
            SELECT  idFailCandidate
            FROM    CandidateIDs r1
            WHERE   NOT EXISTS (SELECT * FROM CandidateIDs WHERE CandidateIDs.idFailCandidate = r1.idOriginal)
        ) fail ON fail.idFailCandidate = YourTable.id           

这篇关于日期重叠情景的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！