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

Transact-SQL
,
1

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

last active reach
last active reach763×423 9.32 KB

2

hi

i tried .. see u like .. if data different .. don't know SQL work

select 
      a.PartIDX 
	, b.PartIDC  
from 
  (	select  
	   zpartid as PartIDX  
	from 
		#acceptedvalues a 
		   join 
	   #LifeCycleMaster b 
			  on a.acceptedvaluesname = 'Obsolete' and a.acceptedvaluesid = b.zlc 
	where 
	   zpartid not in ( select PartIDX from #Replacement)
	   ) a , 
  (	select  
	   zpartid as PartIDC   
	from 
		#acceptedvalues a 
		   join 
	   #LifeCycleMaster b 
			  on a.acceptedvaluesname = 'Active' and a.acceptedvaluesid = b.zlc 
			  ) b

image

3

hi

what happened Ahmed ??

No reply to my solution !!

4

thanks it solve
sorry for late