Tags

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


SQL Puzzle | The Updated By Puzzle – I

This puzzle is a variation in puzzle – https://msbiskills.com/2016/11/24/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 UpdatedBy UpdatedDt UpdatedBy UpdatedDt UpdatedBy UpdatedDt
1 SAM 01/01/1981 MIKE 10/01/2006 JACOB 05/21/2010 JAMES 02/13/2007
2 DAVE 10/20/1978 SHARON 08/15/2015      

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 *
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 * , CONCAT('By',rnk) c1 , CONCAT('Dt',rnk) c2 FROM 
	(
		SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID,DOB) rnk FROM CTE
	)p 
)
SELECT Id, MIN(NAME) NAME, MIN(DOB) DOB , ISNULL(MAX([By1]),'') UpdatedBy 
,ISNULL(MAX([Dt1]),'') UpdatedDt,ISNULL(MAX([By2]),'') UpdatedBy ,ISNULL(MAX([Dt2]),'')UpdatedDt,ISNULL(MAX([By3]),'') UpdatedBy
,ISNULL(MAX([Dt3]),'') UpdatedDt FROM CTE2
PIVOT ( MAX(UPDATED_BY) FOR c1 IN ([By1],[By2],[By3]) )y1
PIVOT ( MAX(UPDATE_DT) FOR c2 IN ([Dt1],[Dt2],[Dt3]) )y2
GROUP BY Id

--

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