SQLTeam.com | Weblogs | Forums

How to give number to every group of steps?

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

I modified script above for steps
Now what I need is to update steps by unique number identity
steps group
1 1
2 1
3 1
1 2
2 2
3 2
1 3
2 3
3 3
so i need to do
update r set r.GroupNo=max + 1 from #Replacement r to rows steps from( 1 to 3) as above