Need to increment a column based on same status

Question

Need to increment the values based on status and sub status column. Original Record:

After Updting:

I am able to update num column for specific member again count values are continuing for the other members also.

Code:

DECLARE @KPI TABLE ( ID INT, ID1 INT, Status_Col VARCHAR(50), Sub_Status_Col VARCHAR(100), Date_Col DATETIME, num INT DEFAULT(1) ) INSERT INTO @KPI (ID,ID1,Status_Col,Sub_Status_Col,Date_Col) VALUES (1,1,'Open','',GETDATE()-3), (1,2,'Completed','Done',GETDATE()-2), (1,3,'Attempted','',GETDATE()-1), (1,4,'Cancelled','',GETDATE()), (1,5,'Completed','Not completed',GETDATE()+1), (1,6,'Open','',GETDATE()+2), (1,7,'Completed','Done',GETDATE()+3), (2,1,'Open','',GETDATE()-3), (2,2,'Completed','Done',GETDATE()-2), (2,3,'Attempted','',GETDATE()-1) SELECT ID,ID1,Status_Col,Sub_Status_Col,Date_Col,num FROM @KPI DECLARE @id INT SET @id = 1 UPDATE A SET @id = num = CASE WHEN Status_Col = 'Completed' AND Sub_Status_Col = 'Done' THEN @id + 1 ELSE @id END FROM @KPI AS A --SELECT * FROM @KPI DROP TABLE IF EXISTS #KPITEMP SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Date_Col) AS VN,* INTO #KPITEMP FROM @KPI WHERE Status_Col = 'Completed' AND Sub_Status_Col = 'Done' UPDATE B SET B.num = A.VN FROM #KPITEMP AS A INNER JOIN @KPI AS B ON A.ID = B.ID AND A.ID1 = B.ID1 SELECT * FROM @KPI

Answer 1

Check a query:

;
with Q as
(
	select *, lag(Sub_Status_Col) over (partition by ID order by Date_Col) as ps
	from @KPI
)
select ID, ID1, Status_Col, Sub_Status_Col, Date_Col, 
	count(case when ps = 'Done' then 0 end) over (partition by ID order by Date_Col) + 1 as num
from Q
order by ID, Date_Col

Answer 2

Hi @Govarthanan Venkatesan

Try this query:

;WITH CTE AS
(
 SELECT *,CASE WHEN LAG(Status_Col)OVER(PARTITION BY ID ORDER BY Date_Col) = 'Completed' AND LAG(Sub_Status_Col)OVER(PARTITION BY ID ORDER BY Date_Col) = 'Done' 
               THEN 1 ELSE 0 END AS To_Sum
 FROM @KPI 
)
SELECT *,num+SUM(To_Sum)OVER(PARTITION BY ID ORDER BY Date_Col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS New_Num
FROM CTE

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.