add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
(No column name)
Microsoft SQL Server 2019 (RTM-CU1) (KB4527376) - 15.0.4003.23 (X64)
Dec 6 2019 14:53:33
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
CREATE TABLE #zzz_Overlapping(

[ID] [varchar](500) NULL,

[PFX] [varchar](500) NULL,

[EFF_DT] [date] NULL,

[TERM_DT] [date] NULL

)


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

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

1 rows affected
insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

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


1 rows affected
insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

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


1 rows affected
insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

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


1 rows affected
insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

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


1 rows affected
insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

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


1 rows affected
insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

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


1 rows affected
insert into #zzz_Overlapping (ID,PFX,EFF_DT,TERM_DT)

values ( 417,'CSM2','2001-11-01','9999-12-31')
1 rows affected
select o.*
from #zzz_Overlapping o
ID PFX EFF_DT TERM_DT
123 ABDM 1997-06-01 9999-12-31
123 ABDM 1997-10-01 9999-12-31
123 CSM1 1997-11-01 9999-12-31
123 CSM1 1998-01-01 9999-12-31
123 ABDD 1999-01-01 9999-12-31
417 CSM1 2001-06-01 9999-12-31
417 CSM1 2001-10-01 9999-12-31
417 CSM2 2001-11-01 9999-12-31
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
)

rnk ID PFX EFF_DT TERM_DT
1 123 ABDM 1997-06-01 9999-12-31
2 123 ABDM 1997-10-01 9999-12-31
3 123 CSM1 1997-11-01 9999-12-31
4 123 CSM1 1998-01-01 9999-12-31
6 417 CSM1 2001-06-01 9999-12-31
7 417 CSM1 2001-10-01 9999-12-31