SQL Puzzle | Number to call Puzzle

25 Thursday Jan 2018

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

Tags

SQL Puzzle | Number to call Puzzle

In this puzzle you have to Generate a new column called Nums(Number to call).

The conditions to generate the new column (Nums) are-

1. If the EmpMobileNumber is not null then take the value from the EmpMobileNumber and append ‘EmpMobileNumber’ to the data.
2. If the EmpResidenceNumber is not null then take the value from the EmpResidenceNumber and append ‘EmpResidenceNumber’ to the data.
3. If both EmpMobileNumber and EmpResidenceNumber are NOT NULL then take the value from the EmpMobileNumber and append ‘EmpMobileNumber’ to the data.
4. If both EmpMobileNumber and EmpResidenceNumber are NULL then we should get NULL as output.
5. The challenge is to do this without USING CASE Statement.

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

Sample Input

EmpId	EmpName	EmpMobileNumber	EmpResidenceNumber
1	Avtaar	9873686533	NULL
2	Kishan	NULL	9873686513
3	Chander	9873682233	9873680233
4	Sharlee	9873111133	7873682233
5	Ishu	NULL	9873686598
6	Ram	9873761211	NULL
7	Vaibhav	NULL	NULL
8	Mayank	NULL	8761231231

Expected Output

EmpId	EmpName	Nums
1	Avtaar	9873686533 MobileNumber
2	Kishan	9873686513 ResidenceNumber
3	Chander	9873682233 MobileNumber
4	Sharlee	9873111133 MobileNumber
5	Ishu	9873686598 ResidenceNumber
6	Ram	9873761211 MobileNumber
7	Vaibhav	NULL
8	Mayank	8761231231 ResidenceNumber

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE EmployeeNumber
(
	 EmpId INT 
	,EmpName VARCHAR(10)
	,EmpMobileNumber VARCHAR(100)
	,EmpResidenceNumber VARCHAR(100)
)
GO

INSERT INTO EmployeeNumber VALUES
(1,'Avtaar','9873686533',NULL),
(2,'Kishan',NULL,'9873686513'),
(3,'Chander','9873682233','9873680233'),
(4,'Sharlee','9873111133','7873682233'),
(5,'Ishu',NULL,'9873686598'),
(6,'Ram','9873761211',NULL),
(7,'Vaibhav',NULL,NULL),
(8,'Mayank',NULL,'8761231231')
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution – 1 | WITHOUT CASE Statement

--

SELECT EmpId , EmpName
	, ISNULL ( EmpMobileNumber + ' MobileNumber' , EmpResidenceNumber + ' ResidenceNumber' ) Nums
FROM EmployeeNumber


--

Output-1 | WITHOUT CASE Statement

--                            

EmpId       EmpName    Nums                                
----------- ---------- ----------------------------------- 
1           Avtaar     9873686533 MobileNumber             
2           Kishan     9873686513 ResidenceNumber          
3           Chander    9873682233 MobileNumber             
4           Sharlee    9873111133 MobileNumber             
5           Ishu       9873686598 ResidenceNumber          
6           Ram        9873761211 MobileNumber             
7           Vaibhav    NULL                                
8           Mayank     8761231231 ResidenceNumber          
                                                           
(8 rows affected)                                          
                                                           
                                  
                                
--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MsbiSkills.com

SQL Puzzle | The String Puzzle [Split Puzzle]

25 Friday Nov 2016

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 3 Comments

Tags

SQL Puzzle | The String Puzzle [Split Puzzle]

Write a T-SQL to fetch first Name, Initial, Last Name and Suffix from Full Name Column.
Please check out the sample input and the expected output.

Sample Inputs

EmployeeID	FullName
1	Nancy Davolio
2	Andrew W Fuller Jr
3	Janet Leverling
4	Margaret Peacock
5	Steven Buchanan
6	Michael J Suyama Jr
7	Robert T King III
8	Laura Callahan
9	Anne Dodsworth

Expected Output

EmployeeID	FullName	FirstName	Initial	LastName	Suffix
1	Nancy Davolio	Nancy		Davolio
2	Andrew W Fuller Jr	Andrew	W	Fuller	Jr
3	Janet Leverling	Janet		Leverling
4	Margaret Peacock	Margaret		Peacock
5	Steven Buchanan	Steven		Buchanan
6	Michael J Suyama Jr	Michael	J	Suyama	r
7	Robert T King III	Robert	T	King	III
8	Laura Callahan	Laura		Callahan
9	Anne Dodsworth	Anne		Dodsworth

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 [dbo].[EmployeesTest](
	[EmployeeID] [int] NOT NULL,
	[FullName] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (1, N'Nancy Davolio')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (2, N'Andrew W Fuller Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (3, N'Janet Leverling')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (4, N'Margaret Peacock')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (5, N'Steven Buchanan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (6, N'Michael J Suyama Jr')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (7, N'Robert T King III')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (8, N'Laura Callahan')
GO
INSERT [dbo].[EmployeesTest] ([EmployeeID], [FullName]) VALUES (9, N'Anne Dodsworth')
GO

--

—

SOLUTION – 1


--

SELECT EmployeeID,FullName, FirstName,Initial , REPLACE(LName,CASE WHEN rt > 0 
THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END,'') LastName, 
CASE WHEN rt > 0 THEN SUBSTRING(LName,rt,LEN(LName)) ELSE '' END Suffix FROM 
(
	SELECT *, RTRIM(LTRIM(REPLACE(LastName,Initial,''))) LName , 
                  CHARINDEX(N' ', RTRIM(LTRIM(REPLACE(LastName,Initial,''))),0) rt
	FROM 
	(
		SELECT *, SUBSTRING(RTRIM(LTRIM(LastName)) , 0, CHARINDEX(N' ',RTRIM(LTRIM(LastName)),1) ) Initial 
		FROM 
		(
			SELECT *, SUBSTRING(FullName , 0, CHARINDEX(N' ',FullName,1) ) FirstName				
					, SUBSTRING(FullName , CHARINDEX(N' ',FullName,1) , DATALENGTH(FullName) ) LastName
			FROM [EmployeesTest]
		)l
	)n
)b

--

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

SQL Puzzle | The Updated By Puzzle – I

24 Thursday Nov 2016

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ Leave a comment

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

SQL Puzzle | The Updated By Puzzle

24 Thursday Nov 2016

Posted by Pawan Kumar Khowal in SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 1 Comment

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

SQL Puzzle | Get Duplicate Rows from Multiple Columns

23 Wednesday Nov 2016

Posted by Pawan Kumar Khowal in Download SQL Interview Q's, SQL Puzzles, SQL SERVER, SQL Server Interview Questions, SQL SERVER Puzzles, T SQL Puzzles, Tricky SQL Queries

≈ 2 Comments

Tags

SQL Puzzle | Get Duplicate Rows from Multiple Columns

Write a T-SQL query that fetches duplicate Rows from Multiple Columns like col1,col2,col3,col4

Please check out the sample input and the expected output.

Sample Inputs

ID	col1	col2	col3	col4
1	a	b	c	d
2	a	b	c	d
3	a	b	c	d
4	g	h	i	j

Expected Output

Id	col1	col2	col3	col4
1	a	b	c	d
2	a	b	c	d
3	a	b	c	d

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 testdupli
(
	ID Int
	,col1 varchar(1)
	,col2 varchar(1)
	,col3 varchar(1)
	,col4 varchar(1)
)
GO

INSERT INTO testdupli VALUES
(1 ,'a', 'b' ,'c', 'd'),
(2 ,'a', 'b' ,'c', 'd'),
(3 ,'a', 'b' ,'c', 'd'),
(4 ,'g', 'h' ,'i', 'j')


--

—

SOLUTION – 1


--

SELECT Id,col1,col2,col3,col4
FROM
(
	SELECT *, COUNT(*) OVER (PARTITION BY col1,col2,col3,col4) rnk
	FROM testdupli
)b
WHERE b.rnk > 1

--

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

Improving my SQL BI Skills

Tag Archives: Multiple Column Pivoting Puzzle

Share this

Share this

Share this

Share this

Share this