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

Advertisements