Increment a counter based on change in value of another column using SQL

Question

I have data similar to below.

UID  EventId  Status  
-------------------
1       1       C
1       2       D
1       3       D
1       4       C
1       5       B
2       1       A
2       2       A   
2       3       D    
2       4       C
3       1       D

I need to create a status_counter like below. Status counter is incremented every time there is a change in the status. Has anyone worked on something like this. Can someone help me out here. Appreciate your help.

UID  EventId  Status  Status_Counter
-------------------------------------
1       1       C         1
1       2       D         2
1       3       D         2
1       4       C         3
1       5       B         4
2       1       A         1
2       2       A         1   
2       3       D         2    
2       4       C         3
3       1       D         1

Felix Pamittan · Accepted Answer · 2015-09-17 02:42:05Z

2

Using ROW_NUMBER and DENSE_RANK:

SQL Fiddle

;WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(PARTITION BY UID ORDER BY EventId)
                - ROW_NUMBER() OVER(PARTITION BY UID, Status ORDER BY EventId)
    FROM tbl
)
SELECT
    UID, EventId, Status,
    Status_Counter = DENSE_RANK() OVER(PARTITION BY UID ORDER BY rn)
FROM Cte
ORDER BY UID, EventId

answered Sep 17, 2015 at 2:42

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bob Over a year ago

This won't work because when order by rn in the dense_rank the order for the statuses would change resulting in wrong output

Felix Pamittan Over a year ago

@PruBon What do you mean? Have you checked the fiddle?

Gordon Linoff · Accepted Answer · 2015-09-17 02:41:36Z

1

The logic can be encapsulated as a cumulative count distinct:

select t.*,
       count(distinct status) over (partition by uid order by eventid) as Status_Counter
from table t;

But, SQL Server doesn't quite support this. So, in SQL Server 2012+:

select t.uid, t.eventid, t.status,
       sum(case when lagstatus = status then 0 else 1 end) over (partition by uid order by eventid) as status_counter
from (select t.*, 
             lag(status) over (partition by uid order by eventid) as lagstatus
      from table t
     ) t;

edited Sep 17, 2015 at 2:41

answered Sep 17, 2015 at 2:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Bob Over a year ago

This is giving me an output like this UID EventId Status Status_Counter 1 ----- 1 ----- C ----- 1 1 ----- 2 ----- D ----- 1 1 ----- 3 ----- D ----- 0 1 ----- 4 ----- C ----- 1 1 ----- 5 ----- B ----- 1 2 ----- 1 ----- A ----- 1 2 ----- 2 ----- A ----- 0 2 ----- 3 ----- D ----- 1 2 ----- 4 ----- C ----- 1 3 ----- 1 ----- D ----- 1

Felix Pamittan Over a year ago

MS should really implement COUNT(DISTINCT) OVER.

PlayMa256 · Accepted Answer · 2015-09-17 03:04:25Z

0

The better way to work when you need to do stuff during an update, is to use a trigger during update.

CREATE TRIGGER update_on_status_change 
ON putYourTableNameHere
FOR UPDATE
AS
DECLATE @event_id
@event_id = SELECT event_id FROM UPDATED
if UPDATE(status)
    BEGIN
        UPDATE otherTableWithCounter SET status_counter += 1 WHERE event_id = @event_id
    END

Theres a lot of time i dont work with triggers, i could have made a mistake.

answered Sep 17, 2015 at 3:04

PlayMa256

6,8512 gold badges40 silver badges58 bronze badges

1 Comment

Bob Over a year ago

select UID, EventID, Status, sum(case when lagstatus = Status then 0 else 1 end) as status_counter INTO #tbl1 from (select t.*, lag(Status) over (partition by UID order by EventID) as lagstatus from TABLE_MAIN t ) t GROUP BY UID,EventID, Status select UID, EventID ,ROW_NUMBER() OVER(PARTITION BY UID ORDER BY EventID) as COUNTER into #FinalTable2 from #FINALTABLE1 where status_counter=1 Select f1.*,COUNTER from TABLE_MAIN f1 left join #tbl1 f2 ON f1.UID= f2.UID and f1.EventID=f2.EventID I did the above steps &Then I copied the Counter to other null values which were resulted by left join

Collectives™ on Stack Overflow

Increment a counter based on change in value of another column using SQL

3 Answers 3

2 Comments

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

1 Comment

Related