Check if periods overlap

IgorM 61 Reputation points
2022-10-29T20:25:08.73+00:00

Hi,

I need to write a rule that for every given CONTRACTCODE will check if any periods defined by STARTDATE and ENDDATE overlap. In essence, the periods should NOT overlap, so the purpose of this rule is to identify contract codes with periods that do overlap.

In the example below, contract code RO_AGN_1 and RO_AGN_2 are OK since none of the periods overlap. However contract code RO_AGN_3 has overlapping periods (2015-05-31–2017-05-31 overlaps 2016-04-30–2017-04-30) and therefore should be returned by the rule. 

CREATE TABLE #TestDate(  
	CONTRACTCODE NVARCHAR(50),  
	STARTDATE DATETIME,  
	ENDDATE DATETIME  
)  
  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_1', {d '2017-12-31'}, {d '2019-12-31'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_1', {d '2020-12-31'}, {d '2020-12-31'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_1', {d '2021-12-31'}, {d '2027-12-31'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_2', {d '2020-06-30'}, {d '2021-06-30'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_2', {d '2021-07-31'}, {d '2023-07-31'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_3', {d '2015-05-31'}, {d '2017-05-31'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_3', {d '2016-04-30'}, {d '2017-04-30'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_3', {d '2018-04-30'}, {d '2019-04-30'})  
INSERT INTO #TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_3', {d '2020-04-30'}, {d '2021-04-30'})  
  
SELECT * FROM #TestDate
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,558 questions
0 comments
Accepted answer
  1. Erland Sommarskog 101.8K Reputation points MVP
    2022-10-29T21:03:30.59+00:00 
    SELECT *   
FROM   #TestDate a  
WHERE  EXISTS (SELECT *  
               FROM   #TestDate b  
               WHERE  b.CONTRACTCODE = a.CONTRACTCODE  
                 AND  b.STARTDATE    <= a.ENDDATE  
                 AND  b.ENDDATE      >= a.STARTDATE  
                 AND  NOT (b.STARTDATE = a.STARTDATE AND  
                           b.ENDDATE    = a.ENDDATE))

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2022-10-29T23:02:24.557+00:00

    Hi,

    Your table structure is problematic since you have no way to identify rows if the columns has the same value

    For example, let's add these two rows: 

    INSERT INTO TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_99', {d '2020-01-11'}, {d '2021-02-11'})  
INSERT INTO TestDate (CONTRACTCODE, STARTDATE, ENDDATE) VALUES ('RO_AGN_99', {d '2020-01-11'}, {d '2021-02-11'})

    If you had for example identification column like ID INT IDENTITY primary key then the solution was simple (very very very close to Erland solution) 

    SELECT *   
FROM   TestDate a  
WHERE  EXISTS (  
 SELECT *  
 FROM TestDate b  
 WHERE   
 b.CONTRACTCODE = a.CONTRACTCODE  
 AND  b.STARTDATE    <= a.ENDDATE  
 AND  b.ENDDATE      >= a.STARTDATE  
 AND NOT a.id = b.id  
 )  
GO

    Unfortunately since you have no such column, you can use something like ROW_NUMBER function to add identification on the fly. Here is a solution for your case without identification column 

    ;With MyCTE as (  
  SELECT CONTRACTCODE, STARTDATE, ENDDATE, ID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))  
  FROM   TestDate a  
 )  
 SELECT CONTRACTCODE, STARTDATE, ENDDATE  
 FROM   MyCTE a  
 WHERE  EXISTS (  
                 SELECT *  
                 FROM MyCTE b  
                 WHERE   
                     b.CONTRACTCODE = a.CONTRACTCODE  
                     AND  b.STARTDATE    <= a.ENDDATE  
                     AND  b.ENDDATE      >= a.STARTDATE  
                     AND NOT a.id = b.id  
             )  
 GO

    Please check if this solve your need

  2. IgorM 61 Reputation points
    2022-10-30T11:05:03.577+00:00

    Hi,

    thank you all for a prompt reply.

    ErlandSommarskog is right. The actual model does contain a row guid column. I did not include it in my initial question as I wanted to keep it as simple as possible.
    Thus, I have taken ErlandSommarskog query and modified it as follows. 

    SELECT *   
 FROM   #TestDate a  
 WHERE  EXISTS (SELECT *  
                FROM   #TestDate b  
                WHERE  b.CONTRACTCODE = a.CONTRACTCODE  
                  AND  b.STARTDATE    <= a.ENDDATE  
                  AND  b.ENDDATE      >= a.STARTDATE  
                  AND  NOT b.RowGuid = a.RowGuid)

    This is very similar to the query suggested by pituach.
    The query seems to be working as desired.

    Thank you all again.