Tags

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


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

ID NAME DOB UPDATED_BY UPDATE_DT
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

Expected Output

ID NAME DOB UPDATED_BY UPDATE_DT NAME DOB
1 SAM 01/01/1981 JACOB 05/21/2010 SAMIEL 01/01/1982
1 SAMIEL 01/01/1982 JAMES 02/13/2007 SAMUEL 01/01/1982
1 SAMUEL 01/01/1982        
2 DAVE 10/20/1978      

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

Advertisements