CODEWITHSUNDEEP
sqlsql-server
Bob
Bob

Reputation: 375

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

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

Upvotes: 0

Views: 1190

Answers (3)

PlayMa256
PlayMa256

Reputation: 6831

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.

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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;

Upvotes: 1

Related Questions