Need to identify overlapping dates

I need to find overlapping dates for a table. So the basic structure is ID, PFX, Start date, and end date. So where the PFX for a given ID is overlapping with another PFX for each ID.

I tried this and had no luck. https://www.sqlservercentral.com/forums/topic/how-to-extract-overlapping-date-ranges-from-a-table-of-date-ranges

The highlighted is what I would want to show as an overlap.

Drop table #zzz_Overlapping
CREATE TABLE #zzz_Overlapping(

    [ID] [varchar](500) NULL,

    [PFX] [varchar](500) NULL,

    [EFF_DT] [datetime] NULL,

    [TERM_DT] [datetime] NULL

) ON [PRIMARY]

GO

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 123,'ABDM','1997-06-01','9999-12-31')

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 123,'ABDM','1997-10-01','9999-12-31')

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 123,'CSM1','1997-11-01','9999-12-31')

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 123,'CSM1','1998-01-01','9999-12-31')

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 123,'ABDD','1999-01-01','9999-12-31')

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 417,'CSM1','2001-06-01','9999-12-31')

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 417,'CSM1','2001-10-01','9999-12-31')

insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 417,'CSM2','2001-11-01','9999-12-31')

select * from #zzz_Overlapping

select CONCAT(ID, PFX) FROM #zzz_Overlapping

SELECT A.*

FROM #zzz_Overlapping A

JOIN #zzz_Overlapping B

    ON B.ID = A.ID

    AND ((A.EFF_DT BETWEEN B.EFF_DT AND B.TERM_DT)

    OR (A.TERM_DT BETWEEN B.EFF_DT AND B.TERM_DT))

WHERE B.EFF_DT != A.EFF_DT OR B.TERM_DT != A.TERM_DT;


SELECT A.ID, A.PFX, A.EFF_DT, A.TERM_DT, B.PFX, B.EFF_DT, B.TERM_DT

FROM #zzz_Overlapping A

INNER JOIN #zzz_Overlapping B

    ON B.ID = A.ID

    AND B.EFF_DT < A.TERM_DT

    AND A.EFF_DT < B.TERM_DT
    AND A.EFF_DT < B.EFF_DT;

Added this for some clarification of expected result.

Answer

Improving upon the answer by Gordon, Can you check the following

with data
  as (select row_number() over(order by (select null)) as rnk
            ,*
         from #zzz_overlapping
      )
select o.*
  from data o
 where exists (select 1
                 from data o2
                where o2.id = o.id and
                    o2.PFX = o.PFX and
                    o2.EFF_DT <= o.TERM_DT and
                    o2.TERM_DT >= o.EFF_DT and
                    o2.rnk <> o.rnk
             )

dbfiddle link. https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=11f4e9dc6bd96df6f0c8145df0b953a8

Advertisement

Answer