SQLTeam.com | Weblogs | Forums

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

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 ?

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 

image
image

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

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

hi ahmed

thank you very much .. for explaining !!

Now
I will try to come up with Solution

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 

image

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

are there are any thing not clear

hi ahmed

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

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

image

1 Like

OK thanks it solved