SQL Puzzle | The A & B Puzzle

Write a T-SQL to fetch A records with condition [ if there is A then show B ] till the last B present in the table

For more please check out the sample input and the expected output.

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table and insert some sample data

--

CREATE TABLE tstNextValue 
(	
	 C1 VARCHAR(1)  
	,C2 DATETIME	
	,C3 DATETIME
)
GO

INSERT INTO tstNextValue VALUES ('A'      ,'12-Apr-13' , '2016/04/12 09:27:20 AM')

INSERT INTO tstNextValue VALUES ('B'      ,'12-Apr-13' , '2016/04/12 09:27:34 AM')  
INSERT INTO tstNextValue VALUES ('A'      ,'12-Apr-13' , '2016/04/13 09:27:34 AM')  
INSERT INTO tstNextValue VALUES ('A'      ,'14-May-13' , '2016/04/15 09:17:31 AM')  
INSERT INTO tstNextValue VALUES ('B'      ,'14-May-13' , '2016/04/15 09:17:38 AM')  
INSERT INTO tstNextValue VALUES ('A'      ,'22-May-13', '2016/04/28 09:24:14 AM')  
INSERT INTO tstNextValue VALUES ('B'      ,'4-Jun-13' , '2016/05/10 11:28:24 AM')   
INSERT INTO tstNextValue VALUES ('A'      ,'14-Jun-13', '2016/05/16 09:30:45 AM')  
INSERT INTO tstNextValue VALUES ('B'      ,'22-Jun-13', '2016/05/16 11:00:25 AM')
GO

--

—

SOLUTION – 1

--

;WITH CTE AS
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY C1 ORDER BY C3) rnk
	,ROW_NUMBER() OVER (PARTITION BY C2 ORDER BY C3) rnk1
	FROM  tstNextValue
)
,CTE1 AS
(
	SELECT C.c1 aStats, c.C2 aDate , p.C2 bStats , p.C2 bDate ,c.rnk ,P.rnk1 FROM CTE c 
	OUTER APPLY
	(
		SELECT * FROM CTE c1 WHERE c1.C1 = 'B'  AND c1.C2 = c.C2 AND c.rnk >= c1.rnk AND c.rnk1 <= c1.rnk1 
	)p
	WHERE c.C1 = 'A'
)
SELECT aStats, aDate , bStats , bDate FROM CTE1 WHERE rnk <= (SELECT MAX(rnk) FROM CTE1 WHERE rnk1 IS NOT NULL)

--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com