How to get active part for obsolete parts on part ID x on replacement table?

Question

I work on SQL server 2012 I face issue I can't get Active Parts for Obsolete Parts

create table #Replacement  
(  
PartIDC  INT,  
PartIDX  INT  
)  
insert into #Replacement  
(PartIDC,PartIDX)  
values   
(1222,3421),  
(3421,5643),  
(5643,2243),  
(2243,3491)  
  
create table #LifeCycleMaster  
(  
ZpartId  int,  
Zlc  int  
)  
insert into #LifeCycleMaster  
(ZpartId,Zlc)  
values  
(1222,2000),  
(3421,2000),  
(5643,2000),  
(2243,2000),  
(3491,2001)  
Create table #acceptedvalues  
(  
acceptedvaluesid int,  
acceptedvaluesname nvarchar(50)  
)  
insert into #acceptedvalues  
values  
(2000,'Obsolete'),  
(2001,'Active')  

I get first Part Obsolete so when get first Part Obsolete will be 1222

then I search for active Part for obsolete Part 1222 on Part X i found it not on same row

but I found it on row number 4 and value on it on Part X as 3491 as Active

so How to do that ?

Expected Result :

PartIDX   PartIDC  
1222      3491  

Answer 1

Check this query:

;
with A as
(
    select ZpartId 
    from #LifeCycleMaster as m
    inner join #acceptedvalues as v on v.acceptedvaluesid = m.Zlc
    where v.acceptedvaluesname = 'Active'
), 
O as
(
    select ZpartId 
    from #LifeCycleMaster as m
    inner join #acceptedvalues as v on v.acceptedvaluesid = m.Zlc
    where v.acceptedvaluesname = 'Obsolete'
), 
T as
(
    select PartIDX as active_id, *, 1 as [level]
    from #Replacement 
    where PartIDX in (select * from A) 
    union all
    select t.active_id, r.*, t.[level] + 1
    from #Replacement as r
    inner join T on T.PartIDC = r.PartIDX
    where r.PartIDX in (select * from O)
)
select t1.PartIDC, t1.active_id as PartIDX
from T as t1
left join T as t2 on t2.active_id = t1.active_id and t2.[level] = t1.[level] + 1 
where t2.active_id is null
option (maxrecursion 197)

Answer 2

Hi @ahmed salah ,

Please refer below:

 drop table if exists #Replacement  
 drop table if exists #LifeCycleMaster  
 drop table if exists #acceptedvalues  
 drop table if exists #temp1  
  
create table #Replacement  
 (  
 PartIDC  INT,  
 PartIDX  INT  
 )  
 insert into #Replacement  
 (PartIDC,PartIDX)  
 values   
 (1222,3421),  
 (3421,5643),  
 (5643,2243),  
 (2243,3491)  
      
 create table #LifeCycleMaster  
 (  
 ZpartId  int,  
 Zlc  int  
 )  
 insert into #LifeCycleMaster  
 (ZpartId,Zlc)  
 values  
 (1222,2000),  
 (3421,2000),  
 (5643,2000),  
 (2243,2000),  
 (3491,2001)  
 Create table #acceptedvalues  
 (  
 acceptedvaluesid int,  
 acceptedvaluesname nvarchar(50)  
 )  
 insert into #acceptedvalues  
 values  
 (2000,'Obsolete'),  
 (2001,'Active')  
  
select a.ZpartId, b.acceptedvaluesname  
into #temp1  
from #LifeCycleMaster as a  
inner join #acceptedvalues as b on b.acceptedvaluesid = a.Zlc  
  
 ;with cte as(  
  select *  
  ,lead (PartIDC) over (order by  (select null))  lead   
  , lag(PartIDC) over (order by  (select null))  lag   
   from #Replacement  
   where PartIDC in (select ZpartId from #temp1 where acceptedvaluesname='Obsolete'))  
  
   select  PartIDC,PartIDX=(select PartIDX  
   from #Replacement  
   where  PartIDX in (select ZpartId from #temp1 where acceptedvaluesname='Active'))    
   from cte   
   where lag is null and lead=PartIDX  
     

Output:

PartIDC PartIDX  
1222 3491   
   

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.