Tags

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


T-SQL Query | [ The Send & Ack Puzzle ]

Original puzzle link – http://beyondrelational.com/blogs/tc/archive/2009/06/04/tsql-challenge-9.aspx

The puzzle is simple. In this puzzle we were required to find the first and last IDs for consecutive rows with same values of Send and Ack states. Please check out the sample input and expected output for details.

Sample Input

ID CreationDate Content SendState AckState
1 24-11-2013 Msg #1 0 0
2 24-11-2013 Msg #2 0 0
3 24-11-2013 Msg #3 1 1
4 24-11-2013 Msg #4 1 1
5 24-11-2013 Msg #5 1 1
6 25-11-2013 Msg #6 1 0
7 25-11-2013 Msg #7 1 0
8 25-11-2013 Msg #8 1 0
9 25-11-2013 Msg #9 1 0
10 25-11-2013 Msg #10 1 1

Expected output

ID ID SendState AckState
1 2 0 0
3 5 1 1
6 9 1 0
10 10 1 1

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

Script

Use the below script to generate the source table and fill them up with the sample data.


--

CREATE TABLE Grouper
(
ID INT IDENTITY(1,1),
CreationDate DATETIME,
Content NVARCHAR(10),
SendState BIT,
AckState BIT
)

INSERT INTO Grouper (CreationDate,Content,SendState,AckState)
SELECT GETDATE()-1.0,'Msg #1',0,0 UNION
SELECT GETDATE()-0.9,'Msg #2',0,0 UNION
SELECT GETDATE()-0.8,'Msg #3',1,1 UNION
SELECT GETDATE()-0.7,'Msg #4',1,1 UNION
SELECT GETDATE()-0.6,'Msg #5',1,1 UNION
SELECT GETDATE()-0.5,'Msg #6',1,0 UNION
SELECT GETDATE()-0.4,'Msg #7',1,0 UNION
SELECT GETDATE()-0.3,'Msg #8',1,0 UNION
SELECT GETDATE()-0.2,'Msg #9',1,0 UNION
SELECT GETDATE()-0.1,'Msg #10',1,1

---------


UPDATE – 20-Apr-2015 – Solution 1


--

;WITH CTE1 AS 
(
	SELECT g1.*, ROW_NUMBER() OVER (ORDER BY g1.ID) rnk
	FROM Grouper g1 left JOIN Grouper g2 on g1.id + 1 = g2.id and g1.SendState = g2.SendState and g1.AckState  = g2.AckState
	WHERE g2.ID IS NULL
),
CTE2 AS 
(
	SELECT g2.*,ROW_NUMBER() OVER (ORDER BY g2.ID) rnk
	FROM Grouper g1 RIGHT JOIN Grouper g2 on g1.id = g2.id - 1 and g1.SendState = g2.SendState and g1.AckState  = g2.AckState
	WHERE g1.ID IS NULL
)
SELECT c2.ID,c1.ID,c1.SendState,c2.AckState FROM CTE1 c1 INNER JOIN CTE2 c2 on c1.rnk = c2.rnk

--

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

Keep Learning

http://MSBISkills.com

Advertisements