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 | Working Hours Puzzle

27 Sunday Nov 2016

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

≈ Leave a comment

Tags

SQL Puzzle | Working Hours Puzzle

Write a T-SQL that gives us hours worked from 2016-09-01 07:00:00 to 2016-09-02 06:59:99.
by EmployeeType

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

Sample Inputs

EmployeeName	EmployeeType	StartDateTime	EndDateTime
Pawan K	N	2016-09-01 06:00:00.000	2016-09-01 14:00:00.000
Ramesh K	N	2016-09-01 13:00:00.000	2016-09-02 09:00:00.000
Sharlee D	D	2016-09-01 09:00:00.000	2016-09-01 13:00:00.000
Mizan D	D	2016-09-01 20:00:00.000	2016-09-02 08:00:00.000

Expected Output

EmployeeType	WorkDate	Hours
N	2016-08-31	1
N	2016-09-01	25
N	2016-09-02	2
D	2016-09-01	15
D	2016-09-02	1

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 Employees1
(
	 EmployeeName         VARCHAR(100)
	,EmployeeType        VARCHAR(100)       
	,StartDateTime    DATETIME
	,EndDateTime DATETIME
)
GO

INSERT INTO Employees1 VALUES
('Pawan K'            ,          'N'                      ,'2016-09-01 06:00:00'    , '2016-09-01 14:00:00' ),
('Ramesh K'             ,             'N'                 ,     '2016-09-01 13:00:00' ,    '2016-09-02 09:00:00' ),
('Sharlee D'              ,         'D'                    , '2016-09-01 09:00:00'     ,'2016-09-01 13:00:00' ),
('Mizan D'                ,           'D'                  ,   '2016-09-01 20:00:00'    , '2016-09-02 08:00:00' )
GO

SELECT * FROM Employees1
GO

--

—

SOLUTION – 1


--

DECLARE @StartDate AS DATETIME = '2016-01-01'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
,CTE0 AS
(
	SELECT *, DATEADD(d,Number,@StartDate) st FROM Series 
)
,CTE1
AS
(
	SELECT * , CASE WHEN StartDateTime > st then CAST(st AS DATE) else CAST(DATEADD(d,-1,CAST(StartDateTime AS DATE)) AS DATE) END starts
		     , CASE WHEN EndDateTime > et then CAST(et AS DATE) else CAST(DATEADD(d,-1,CAST(EndDateTime AS DATE)) AS DATE) END Ends
	FROM
	(
		SELECT EmployeeType,StartDateTime , EndDateTime , DATEADD(day, DATEDIFF(day, 0, CAST(StartDateTime AS DATE)), '07:00:00') st
		, DATEADD(day, DATEDIFF(day, 0, CAST(EndDateTime AS DATE)), '07:00:00') et
		FROM Employees1
	)k
)
SELECT EmployeeType, CAST(b.st AS DATE) WorkDate
			,SUM(CASE WHEN starts = b.st THEN 
				CASE WHEN a.st > StartDateTime THEN DATEDIFF(hh,StartDateTime,a.st) ELSE 
					CASE WHEN starts <> Ends THEN
							DATEDIFF(hh,StartDateTime,CASE WHEN a.et < EndDateTime THEN a.et ELSE EndDateTime END) 
					ELSE
							DATEDIFF(hh,StartDateTime,EndDateTime)
					END
				END
			ELSE
				CASE WHEN a.et < EndDateTime THEN DATEDIFF(hh,a.et,EndDateTime) ELSE DATEDIFF(hh,a.StartDateTime,a.et)END  
			END) Hours 
FROM CTE1 a
INNER JOIN CTE0 b ON b.st BETWEEN a.starts AND a.Ends
GROUP BY EmployeeType,Number,CAST(b.st AS DATE)
ORDER BY EmployeeType DESC,Number

--

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 | Remove Duplicate Ints From String Puzzle

27 Sunday 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 | Remove Duplicate Ints From String Puzzle

Write a T-SQL to remove the duplicate int values present in the string column. You also have to remove the Single Int character present in the string.

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

Sample Inputs

PawanName	Pawan_slug_name
1	PawanA-111
2	PawanB-123
3	PawanB-32
4	PawanC-4444
5	PawanD-3

Expected Output

PawanName	Pawan_slug_name
1	PawanA
2	PawanB-123
3	PawanB-32
4	PawanC
5	PawanD

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 RemoveDuplicateIntsFromNames
(
	  PawanName INT
	, Pawan_slug_name VARCHAR(1000)
)
GO


INSERT INTO RemoveDuplicateIntsFromNames VALUES
(1,  'PawanA-111'  ),
(2, 'PawanB-123'   ),
(3, 'PawanB-32'    ),
(4, 'PawanC-4444' ),
(5, 'PawanD-3'  )
GO


--

—

SOLUTION – 1


--


;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3   
)
SELECT DISTINCT PawanName , CASE WHEN cnt = dcnt AND cnt >= 1 THEN chrs else Pawan_slug_name END Pawan_slug_name  FROM 
(
	SELECT * , COUNT(SUBSTRING(Ints,Number,1)) OVER (PARTITION BY PawanName) cnt , COUNT(SUBSTRING(Ints,Number,1)) OVER (PARTITION BY PawanName,SUBSTRING(Ints,Number,1)) dcnt FROM 
	(
		SELECT * , SUBSTRING(Pawan_slug_name,0,CHARINDEX('-',Pawan_slug_name,0)) chrs
		, SUBSTRING(Pawan_slug_name,CHARINDEX('-',Pawan_slug_name,0)+1,DATALENGTH(Pawan_slug_name)) Ints  FROM RemoveDuplicateIntsFromNames
	)k
	CROSS APPLY
	( 
		SELECT DISTINCT number FROM
		Series WHERE number > 0 AND number <= DATALENGTH(k.Ints) 
	)x
)m
ORDER BY PawanName 

--

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 Status Puzzle

27 Sunday 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 Status Puzzle

Write a T-SQL to fetch all the rows from table11 and all rows from table21 where status from table11 is 1 and table21 status is 0. We also have to display rows from table11 which are not present in table21

Please check out the sample input and the expected output.

Sample Inputs

Table11

ItemNo	sStatus
Part1	1
Part2	1
Part3	1
Part4	5
Part5	1

Table21

ItemNo	sStatus
Part1	0
Part2	1
Part3	1
Part4	0

Expected Output

ItemNo	sStatus	sstatus
Part1	1	0
Part5	1	0

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 Table11 
(ItemNo VARCHAR(255),sStatus int)
GO

CREATE TABLE Table21 
(ItemNo VARCHAR(255),sStatus int)
GO

INSERT INTO Table11  VALUES ('Part1',1) 
INSERT INTO Table11  VALUES ('Part2',1) 
INSERT INTO Table11  VALUES ('Part3',1) 
INSERT INTO Table11  VALUES ('Part4',5) 
INSERT INTO Table11  VALUES ('Part5',1) 
GO

INSERT INTO Table21  VALUES ('Part1',0) 
INSERT INTO Table21  VALUES ('Part2',1) 
INSERT INTO Table21  VALUES ('Part3',1) 
INSERT INTO Table21  VALUES ('Part4',0) 
GO

SELECT * FROM Table11 
SELECT * FROM Table21

--

—

SOLUTION – 1


--

SELECT 
		a.*,ISNULL(b.sstatus,0) sstatus
FROM table11 a LEFT JOIN table21 b
ON a.itemno = b.itemno
WHERE a.sstatus = 1 and ISNULL(b.sstatus,0) = 0
--

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 Case & Between Puzzle

27 Sunday 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 Case & Between Puzzle

Write a T-SQL to use case and between in a query. In this if the Wording is like ‘%My Wording%’ then we have put a filter on datefields between dates (‘1/1/16’ AND ‘2/1/16’)

Please check out the sample input and the expected output.

Sample Inputs

ID	Wording	DateFields
1	My Wording	2016-01-01 00:00:00.000
2	My Wording	2016-02-01 00:00:00.000
3	Pawan	2016-01-01 00:00:00.000
4	Ramehs	2016-05-01 00:00:00.000
5	My Wording	2016-05-01 00:00:00.000

Expected Output

ID	Wording	DateFields
1	My Wording	2016-01-01 00:00:00.000
2	My Wording	2016-02-01 00:00:00.000
3	Pawan	2016-01-01 00:00:00.000
4	Ramehs	2016-05-01 00:00:00.000

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 Wordings
(
	 ID INT
	,Wording VARCHAR(100)
	,DateFields DATETIME
)
GO

INSERT INTO Wordings VALUES
(1,'My Wording','1/1/16'),
(2,'My Wording','2/1/16'),
(3,'Pawan','1/1/16'),
(4,'Ramehs','5/1/16'),
(5,'My Wording','5/1/16')
GO

--

—

SOLUTION – 1


--

SELECT * FROM Wordings
WHERE     DateFields >=  
			CASE WHEN Wording LIKE '%My Wording%' THEN '1/1/16' ELSE DateFields END
AND 
	  DateFields <=  
			CASE WHEN Wording LIKE '%My Wording%' THEN '2/1/16' ELSE DateFields END

--

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: Circular Prime Number Puzzlre

SQL Puzzle | Working Hours Puzzle

SQL Puzzle | Remove Duplicate Ints From String Puzzle

SQL Puzzle | The Status Puzzle

SQL Puzzle | The Case & Between Puzzle

Share this

Share this

Share this

Share this

Share this