日期重叠情景 [英] 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屋!