Find Broken Loop COnnection from Two Columns


 
 Create table Sample
 (
  Rec_Id Int Not null,
  Name varchar(30) null,
  ID1 varchar(10) null,
  ID2 varchar(10) null,
 CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED 
(
  [Rec_Id] ASC
 ))
  

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
 
;with mycte as
(select Rec_Id,name, id1, id2  
,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
 
 
from sample )
, mycte1 as(
select m0.Rec_id,m0.name, m0.id1,  m0.id2 , Coalesce (m1.id2, m2.id2,'') id2_2, m0.rn2
, Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
from mycte m0 
Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
)

Select Rec_id,name, id1,id2 
from mycte1
WHERE rn2=1 AND cnt>0
Order by Rec_id

/*
Rec_id	name	id1	id2
6	B	B5Y13	NULL
10	C	C6JPQ	C5Z19
13	D	D5Z18	D5Z17

*/



drop table sample

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s