SQL Puzzle | How many times the value of a column has changed?

In this puzzle you have to find out how many time the value of Status column has changed for each Event. For more details please check the sample input and expected output.

Sample Input

Id EventId RecoredTime Status
1 1 2017-10-11 15:35:24.100 T
2 1 2017-10-21 15:35:24.100 T
3 1 2017-10-31 15:35:24.100 T
4 1 2017-11-10 15:35:24.103 F
5 1 2017-11-20 15:35:24.103 T
6 1 2017-11-30 15:35:24.103 F
7 2 2017-10-11 15:35:24.103 F
8 2 2017-10-21 15:35:24.103 F
9 2 2017-10-31 15:35:24.103 T
10 2 2017-11-10 15:35:24.103 F
11 2 2017-10-31 15:35:24.103 T
12 2 2017-11-10 15:35:24.103 F

Expected Output

EventId NumberOfChanges
1 3
2 4

Script

Use below script to create table and insert sample data into it.

 ```-- create table Event (Id int,EventId int,RecoredTime datetime,Status char(1)) GO insert into Event values (1,1,getdate()-100,'T') insert into Event values (2,1,getdate()-90,'T') insert into Event values (3,1,getdate()-80,'T') insert into Event values (4,1,getdate()-70,'F') insert into Event values (5,1,getdate()-60,'T') insert into Event values (6,1,getdate()-50,'F') insert into Event values (7,2,getdate()-100,'F') insert into Event values (8,2,getdate()-90,'F') insert into Event values (9,2,getdate()-80,'T') insert into Event values (10,2,getdate()-70,'F') insert into Event values (11,2,getdate()-80,'T') insert into Event values (12,2,getdate()-70,'F') GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | for SQL SERVER 2012-

 ```-- ;WITH CTE1 AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY EventId ORDER BY Id) rnk FROM [Event] ) ,CTE2 AS ( SELECT *,CASE WHEN [status] = ( SELECT TOP 1 [Status] FROM CTE1 c1 WHERE c2.EventId = c1.EventId AND c1.rnk > c2.rnk ORDER BY rnk ) THEN 0 ELSE 1 END cols FROM CTE1 c2 ) ,CTE3 AS ( SELECT * , SUM(cols) OVER (PARTITION BY EventId ORDER BY Id,rnk) - 1 grouper FROM CTE2 ) SELECT EventId , MAX(grouper) NumberOfChanges FROM CTE3 GROUP BY EventId -- ```

Output-1

 ```-- EventId NumberOfChanges ----------- --------------- 1 3 2 4 (2 rows affected) -- ```

Solution – 2 | for SQL SERVER 2012+

 ```-- ;WITH CTE1 AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY EventId ORDER BY Id) rnk FROM [Event] ) ,CTE2 AS ( SELECT *, CASE WHEN [Status] = LAG([Status]) OVER(PARTITION BY EventId ORDER BY Id) THEN 0 ELSE 1 END cols FROM CTE1 c2 ) ,CTE3 AS ( SELECT * , SUM(cols) OVER (PARTITION BY EventId ORDER BY Id,rnk) - 1 grouper FROM CTE2 ) SELECT EventId , MAX(grouper) NumberOfChanges FROM CTE3 GROUP BY EventId -- ```

Output-1

 ```-- EventId NumberOfChanges ----------- --------------- 1 3 2 4 (2 rows affected) -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MsbiSkills.com