Tags

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


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

C1 C2 C3
A 2013-04-12 00:00:00.000 2016-04-12 09:27:20.000
B 2013-04-12 00:00:00.000 2016-04-12 09:27:34.000
A 2013-04-12 00:00:00.000 2016-04-13 09:27:34.000
A 2013-05-14 00:00:00.000 2016-04-15 09:17:31.000
B 2013-05-14 00:00:00.000 2016-04-15 09:17:38.000
A 2013-05-22 00:00:00.000 2016-04-28 09:24:14.000
B 2013-06-04 00:00:00.000 2016-05-10 11:28:24.000
A 2013-06-14 00:00:00.000 2016-05-16 09:30:45.000
B 2013-06-22 00:00:00.000 2016-05-16 11:00:25.000

Expected Output

aStats aDate bStats bDate
A 2013-04-12 00:00:00.000 2013-04-12 00:00:00.000 2013-04-12 00:00:00.000
A 2013-04-12 00:00:00.000 NULL NULL
A 2013-05-14 00:00:00.000 2013-05-14 00:00:00.000 2013-05-14 00:00:00.000

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

Advertisements