Update incremental group number per group in SQL Server

ahmedsa

SOLVED CLOSED ACTIVE ANSWERED
User: ahmedsa
Posted: on Sep 12, 2020 09:25 PM
Forum: SQL Server
Answer: 1
Views: 2298

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.

select * from #Replacement

Expected result

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 ?

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)

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

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 3

Reply
ANSWER
User: dharmendr
Replied: on Sep 15, 2020 11:03 AM
Report

Refer below query.

SQL

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 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) SrNo,PartIDC,PartIDX,FlagStatus,HasReplacement,groupId,step 
INTO #Test 
FROM #Replacement

DECLARE @Counter INT
SET @Counter = 1
DECLARE @GroupNo INT
SET @GroupNo = 1

WHILE (@Counter <= (SELECT COUNT(*) FROM #Replacement))
BEGIN
    DECLARE @Step INT
	SELECT @Step = step FROM #Test WHERE SrNo = @Counter
	UPDATE #Test SET groupId = @GroupNo WHERE step = @Step AND SrNo = @Counter
	IF @Step >= (SELECT MAX(step) FROM #Test)
	BEGIN
	    SET @GroupNo = @GroupNo + 1
	END
	
    SET @Counter = @Counter  + 1
END

SELECT PartIDC,PartIDX,flagStatus,HasReplacement,groupId GroupNo,step FROM #Test

DROP TABLE #Replacement
DROP TABLE #Test

Output

PartIDC	PartIDX	flagStatus	HasReplacement	GroupNo	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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Update incremental group number per group in SQL Server

Company

Explore

Follow us

Contact Us