I work SQL server 2012 I face issue I can't give Number to every group of steps and separate every steps by it number related .
drop table #Replacement
drop table #LifeCycleMaster
drop table #acceptedvalues
drop table #getActiveParts
drop table #getobsoleteParts
create table #Replacement
(
PartIDC INT,
PartIDX INT,
FlagStatus nvarchar(50),
HasReplacement nvarchar(50),
groupId int,
step tinyint
)
insert into #Replacement
(PartIDC,PartIDX,FlagStatus,HasReplacement, groupId, step)
values
(1222,3421,'Invalid','Critical', Null, 1),
(3421,6421,'Valid','Active', Null, 2),
(5643,2243,'Invalid','Critical', Null, 1),
(2243,3491,'Valid','Active', Null, 2)
create table #LifeCycleMaster
(
ZpartId int,
Zlc int
)
insert into #LifeCycleMaster
(ZpartId,Zlc)
values
(1222,2000),
(3421,2000),
(6421,2001),
(5643,2000),
(2243,2000),
(3491,2001)
Create table #acceptedvalues
(
acceptedvaluesid int,
acceptedvaluesname nvarchar(50)
)
insert into #acceptedvalues
values
(2000,'Obsolete'),
(2001,'Active')
--step 1 get obsolte parts on original and replacement
select r.PartIDC,r.PartIDX,'Invalid' as FlagStatus, 'Critical' as HasReplacement
into #getobsoleteParts
from #Replacement r
inner join #LifeCycleMaster m on r.PartIDC=m.ZpartId
inner join #LifeCycleMaster m2 on r.PartIDX=m2.ZpartId
WHERE m.Zlc=2000 and m2.Zlc=2000
update r set r.step=1 from #Replacement r inner join #getobsoleteParts g
on r.PartIDC=g.PartIDC and r.PartIDX=g.PartIDX
--step 2--
select r.PartIDC,r.PartIDX, 'Valid' as FlagStatus, 'Active' as HasReplacement
into #getActiveParts
from #getobsoleteParts o
inner join #Replacement r on o.PartIDX=r.PartIDC
inner join #LifeCycleMaster m on r.PartIDX=m.ZpartId
WHERE m.Zlc=2001
update r set r.step=2 from #Replacement r inner join #getActiveParts a
on r.PartIDC=a.PartIDC and r.PartIDX=a.PartIDX
--step 3--
insert into #Replacement (PartIDC,PartIDX,FlagStatus,HasReplacement, step)
select o.PartIDC,a.PartIDX,'Valid','Chain', 3
from #getobsoleteParts o
inner join #getActiveParts a on o.PartIDX=a.PartIDC
select * from #Replacement
select * from #Replacement
image Explain steps above
PartIDC | PartIDX | FlagStatus | HasReplacement | GroupNo | Steps |
---|---|---|---|---|---|
1222 | 3421 | Invalid | Critical | 1 | 1 |
3421 | 6421 | Valid | Active | 1 | 2 |
1222 | 6421 | Valid | Chain | 1 | 3 |
5643 | 2243 | Invalid | Critical | 2 | 1 |
2243 | 3491 | Valid | Active | 2 | 2 |
5643 | 3491 | Valid | Chain | 2 | 3 |
Every cycle start by Has Replacement Critical and End With Chain
and start also by Flag status Invalid and End with Valid