Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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