SQL Puzzle | The Updated By Puzzle

Write a T-SQL query that produces the expected output. We have to show who has updated the name and when it was updated.

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

--


;WITH CTE
AS
(SELECT *
FROM (
VALUES 
   (1,'SAM','01/01/1981','MIKE','10/01/2006')
   ,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
   ,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
   ,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
 ) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)
SELECT * FROM CTE

--

—

SOLUTION – 1

--

;WITH CTE
AS
(SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID,DOB) rnk1
FROM (
VALUES 
   (1,'SAM','01/01/1981','MIKE','10/01/2006')
   ,(1,'SAMUEL','01/01/1982','JAMES','02/13/2007')
   ,(1,'SAMIEL','01/01/1982','JACOB','05/21/2010')
   ,(2,'DAVE','10/20/1978','SHARON','08/15/2015')
 ) AS A(ID,NAME,DOB,UPDATED_BY,UPDATE_DT)
)
,CTE2 AS
(	
	SELECT ID,NAME,DOB,UPDATE_DT,rnk FROM 
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID,DOB) rnk FROM CTE
	)p 
)
SELECT a.ID, a.NAME , a.DOB , ISNULL(y.UPDATED_BY,'') UPDATED_BY , ISNULL(y.UPDATE_DT,'') UPDATE_DT , ISNULL(y.NAME,'') NAME, 
ISNULL(y.DOB,'') DOB FROM CTE2 a
OUTER APPLY
(
	SELECT TOP 1 * FROM CTE b
	WHERE a.Id = b.Id AND b.rnk1 > a.rnk
	ORDER BY b.rnk1
)y


--

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