How to make update statement incremental by 1 for group Id from step 1 to 3?

ahmedbarbary · September 13, 2020, 12:17am

I work on SQL server 2012 I face issue i can't update group no to every steps from 1 to 3
meaning i need every group from 1 to 3 take identity number incremental by 1 as 1,2,3 etc ..

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),
(1222,	6421,	'Valid'	,    'Chain',	    NULL,	3),
(5643,	2243,	'Invalid',	'Critical',	NULL,	1),
(2243,	3491,	'Valid'	,    'Active',   NULL,	2),
(5643,	3491,	'Valid'	,    'Chain',	    NULL,	3)

select * from #Replacement

Expected result

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

always step 1 and step 3 are both equal on PartIDC

always step 1 and step 2 are both equal on PartIDx from step 1 equal to PartIDC from step 2 .

so How to do Expected result above by update statement to groupId ?

harishgg1 · September 13, 2020, 6:51am

hi

i tried to do this .. hope this helps !!

; with cte as 
(
     select 
	       *
	   ,  row_number() over(partition by step order by step) as [Group]  
     from 
	    @Replacement
) 
select 
     PartIDC
   , PartIDX
   , FlagStatus
   , HasReplacement
   , [Group]
   , Step
from 
   cte 
order by 
   [Group],step

ahmedbarbary · September 13, 2020, 7:15am

when I change order of rows as below not working and give me wrong result

  insert into #Replacement (PartIDC,PartIDX,FlagStatus,HasReplacement,groupId,step)
	  values 

(1222,	3421,	'Invalid',	'Critical',	NULL,	1),
(1222,	6421,	'Valid'	,    'Chain',	    NULL,	3),
(5643,	2243,	'Invalid',	'Critical',	NULL,	1),
(3421,	6421,	'Valid'	,    'Active' ,   NULL,	2),
(2243,	3491,	'Valid'	,    'Active',   NULL,	2),
(5643,	3491,	'Valid'	,    'Chain',	    NULL,	3)

PartIDC	PartIDX	FlagStatus	HasReplacement	Group	Step
5643	2243	Invalid	Critical	1	1
3421	6421	Valid	Active	1	2
5643	3491	Valid	Chain	1	3
1222	3421	Invalid	Critical	2	1
2243	3491	Valid	Active	2	2
1222	6421	Valid	Chain	2	3

to more clear

suppose I have group id Number 1 or 2 or 3 etc..
then
1-
PartIDC for for first row and third row must be matched and equal
meaning
PartIDC group
1222 1. firstrow
1222 1 third row
and third row must be has replacement Chain

2- PartIDX first row must equal PartID C second row per every group
PartIDC PartIdX groupid
1222 3421 1 first row
3421 1 second row
3- first row must have has replacement critical but others must not have
i start by has replacement critical and finish with chain has replacement

ahmedbarbary · September 13, 2020, 9:55am

I will clear every thing first script

    drop table #Replacement
 drop table #acceptedvalues
 drop table #LifeCycleMaster
 drop table #getobsoleteParts
 drop table #getActiveParts
 drop table #generatechain

 create table #Replacement
    (
    PartIDC  INT,
    PartIDX  INT,
    FlagStatus nvarchar(50),
    HasReplacement nvarchar(50),
	groupId int
    )
    insert into #Replacement
    (PartIDC,PartIDX,FlagStatus,HasReplacement)
    values 
    (1222,3421,'Invalid','Critical'),
    (3421,6421,'Valid','Active'),
	(1222,6421,'Valid','Chain'),
    (5643,2243,'Invalid','Critical'),
    (2243,3491,'Valid','Active'),
	(5643,3491,'Valid','Chain')

    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')
	--drop table #LifeCycleMaster
	--step 1 get obsolte parts on original and replacement
select r.PartIDC,r.PartIDX, FlagStatus,  HasReplacement,1 as step 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
--step 2--
select r.PartIDC,r.PartIDX,  r.FlagStatus,  r.HasReplacement ,2 as step 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

--step 3--

 
select r.PartIDC,r.PartIDX,  r.FlagStatus,  r.HasReplacement ,3 as step into #generatechain   from #getobsoleteParts o
inner join #Replacement r on o.PartIDC=r.PartIDC
where r.HasReplacement='Chain'

select * from #Replacement

select * from #getobsoleteParts
union all
select * from #getActiveParts
union all
select * from #generatechain

Expected Result will be

PartIDC	PartIDX FlagStatus	HasReplacement	groupId	step	
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

harishgg1 · September 13, 2020, 10:05am

hi ahmed

thank you very much .. for explaining !!

Now
I will try to come up with Solution

harishgg1 · September 13, 2020, 11:05am

hi Ahmed

I tried to do this .. Please see if this is correct

... There is one more condition needs to be added .. the third row should have chain
... if this looks ok .. i can add it

; with cte as 
(
select * from #getobsoleteParts
union all
select * from #getActiveParts
union all
select * from #generatechain
) , cte_first as 
(
select * from cte where step =1 
) , cte_second as 
(
select a.PartIDC,a.PartIDX,a.FlagStatus,a.HasReplacement,a.step from cte a join cte_first b on b.PartIDX = a.PartIDC
), cte_third as 
( 
select a.PartIDC,a.PartIDX,a.FlagStatus,a.HasReplacement,a.step  from cte a join cte_second b on b.PartIDX = a.PartIDX where a.step = 3 
) 
select *   from cte_first  
union all
select *   from cte_second 
union all
select *   from cte_third 
go

ahmedbarbary · September 13, 2020, 11:22am

thank you for reply
1- but also remaining group no not exist I need to give every steps from 1 to 3 number
based on relation .
2- also must row arranged to be Partid c on row 1 and 3 to be similar
3- also Partid x and Partid c must be same as similar

expected result
last desired result

ahmedbarbary · September 13, 2020, 11:33am

are there are any thing not clear

harishgg1 · September 13, 2020, 11:55am

hi ahmed

i am starting just now to TRY .. wait .. thank you

harishgg1 · September 13, 2020, 12:00pm

hi

please see if OK .. you like !!

; with cte as 
(
select * from #getobsoleteParts
union all
select * from #getActiveParts
union all
select * from #generatechain
) , cte_first as 
(
select * from cte where step =1 
) , cte_second as 
(
select a.PartIDC,a.PartIDX,a.FlagStatus,a.HasReplacement,a.step from cte a join cte_first b on b.PartIDX = a.PartIDC
), cte_third as 
( 
select a.PartIDC,a.PartIDX,a.FlagStatus,a.HasReplacement,a.step  from cte a join cte_second b on b.PartIDX = a.PartIDX where a.step = 3 
) , cte_fin as 
(
select *   from cte_first  
union all
select *   from cte_second 
union all
select  *   from cte_third 
) 
select  
        ROW_NUMBER() over(partition by step order by step) as grp
	  , * 
from 
   cte_fin 
order by 
  grp,step

ahmedbarbary · September 13, 2020, 12:47pm

OK thanks it solved