SQL Puzzle | The Phone Call Puzzle

10 Monday Oct 2016

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

Tags

SQL Puzzle | The Phone Call Puzzle

Write a T-SQL query that fetches records where the person’s first and last call of the day is to the same person.

Please check out the sample input and the expected output.

Sample Inputs

ID	FromPhoneNumber	ToPhoneNumber	Starttime	EndTime
101	9878650055	9878650002	2016-10-09 12:17:48.483	2016-10-09 12:20:48.483
101	9878650055	9878650000	2016-10-09 13:17:48.483	2016-10-09 13:20:48.483
101	9878650055	9878650008	2016-10-09 14:17:48.483	2016-10-09 14:20:48.483
101	9878650055	9878650000	2016-10-09 15:17:48.483	2016-10-09 15:20:48.483
101	9878650055	9878650002	2016-10-09 20:17:48.483	2016-10-09 20:20:48.483
102	9878650051	9878650099	2016-10-09 12:17:48.483	2016-10-09 12:20:48.483
102	9878650051	9878650099	2016-10-09 12:21:48.483	2016-10-09 12:25:48.483
102	9878650051	9878650099	2016-10-09 12:29:48.483	2016-10-09 12:31:48.483
102	9878650051	9878650077	2016-10-09 20:29:48.483	2016-10-09 20:35:48.483

Expected Output

ID	FromPhoneNumber	ToPhoneNumber	Date
101	9878650055	9878650002	2016-10-09

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 PhoneLog
(
       ID INT,
       FromPhoneNumber Varchar(100),
       ToPhoneNumber Varchar(100),
       Starttime DateTime,
       EndTime DateTime
)
GO
 
INSERT INTO PhoneLog Values 

(101,9878650055,9878650002,'2016-10-09 12:17:48.483','2016-10-09 12:20:48.483'),
(101,9878650055,9878650000,'2016-10-09 13:17:48.483','2016-10-09 13:20:48.483'),
(101,9878650055,9878650008,'2016-10-09 14:17:48.483','2016-10-09 14:20:48.483'),
(101,9878650055,9878650000,'2016-10-09 15:17:48.483','2016-10-09 15:20:48.483'),
(101,9878650055,9878650002,'2016-10-09 20:17:48.483','2016-10-09 20:20:48.483'),
 
(102,9878650051,9878650099,'2016-10-09 12:17:48.483','2016-10-09 12:20:48.483'),
(102,9878650051,9878650099,'2016-10-09 12:21:48.483','2016-10-09 12:25:48.483'),
(102,9878650051,9878650099,'2016-10-09 12:29:48.483','2016-10-09 12:31:48.483'),
(102,9878650051,9878650077,'2016-10-09 20:29:48.483','2016-10-09 20:35:48.483')
 
GO



--

—

SOLUTION – 1


--

SELECT tbl.Id, tbl.FromPhoneNumber, tbl.ToPhoneNumber, tbl.distinctdate Date 
FROM 
(
       SELECT  a.Id, a.FromPhoneNumber, a.ToPhoneNumber, a.Starttime, Format(Starttime, 'MM/dd/yyyy') distinctdate
       FROM    PhoneLog a
                    OUTER APPLY ( SELECT MIN(starttime) CallDateTime
                                          FROM      Phonelog b
                                          WHERE     b.ID = a.ID AND Format(b.Starttime, 'MM/dd/yyyy') = Format(a.Starttime, 'MM/dd/yyyy')
       UNION ALL 
        SELECT MAX(starttime) 
                                          FROM      Phonelog b
                                          WHERE     b.ID = a.ID AND Format(b.Starttime, 'MM/dd/yyyy') = Format(a.Starttime, 'MM/dd/yyyy')
                                        ) C
       WHERE a.Starttime = c.CallDateTime
) tbl
GROUP BY tbl.Id, tbl.FromPhoneNumber, tbl.ToPhoneNumber, tbl.distinctdate
HAVING COUNT(tbl.ToPhoneNumber) = 2


--

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 Greatest Number of Employees (Solution Time:))

30 Friday Sep 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 | The Greatest number of employees

Write a T-SQL query that returns each employee and for each employee include the greatest number of employees that worked for the company at any time during their tenure and the first date that maximum was reached.

Sample Inputs

EmployeeId	DepartmentId	FirstName	LastName	Salary	HireDate	TerminationDate
1	1000	A	Z	300000.000	2001-09-01 00:00:00.000	2010-01-16 00:00:00.000
2	1000	B	Y	350000.000	2003-03-15 00:00:00.000	NULL
3	1000	C	X	500000.000	2004-01-15 00:00:00.000	2010-12-02 00:00:00.000
4	1000	D	W	400000.000	2005-05-15 00:00:00.000	NULL
5	1000	D	Q	200000.000	2006-04-15 00:00:00.000	2008-12-02 00:00:00.000
6	1000	E	P	300000.000	2007-08-01 00:00:00.000	NULL
7	1000	F	N	600000.000	2008-12-01 00:00:00.000	NULL
8	1000	G	O	700000.000	2009-12-01 00:00:00.000	NULL
9	1000	H	P	1000000.000	2010-01-01 00:00:00.000	NULL
11	1000	J	Z	300000.000	2002-09-05 00:00:00.000	2009-12-15 00:00:00.000
10	1000	I	Z	300000.000	2001-09-01 00:00:00.000	2009-12-05 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 Employeex
(
       EmployeeId BIGINT
       ,DepartmentId INT
       ,FirstName VARCHAR(1)
       ,LastName VARCHAR(1)
       ,Salary NUMERIC(10,3)
       ,HireDate DATETIME 
       ,TerminationDate DATETIME 
)
GO

INSERT INTO Employeex VALUES
(1,1000,'A','Z',300000,'2001-09-01 00:00:00.000','2010-01-16 00:00:00.000'),
(2,1000,'B','Y',350000,'2003-03-15 00:00:00.000',NULL),
(3,1000,'C','X',500000,'2004-01-15 00:00:00.000','2010-12-02 00:00:00.000'),
(4,1000,'D','W',400000,'2005-05-15 00:00:00.000',NULL),
(5,1000,'D','Q',200000,'2006-04-15 00:00:00.000','2008-12-02 00:00:00.000'),
(6,1000,'E','P',300000,'2007-08-01 00:00:00.000',NULL),
(7,1000,'F','N',600000,'2008-12-01 00:00:00.000',NULL),
(8,1000,'G','O',700000,'2009-12-01 00:00:00.000',NULL),
(9,1000,'H','P',1000000,'2010-01-01 00:00:00.000',NULL),
(11,1000,'J','Z',300000,'2002-09-05 00:00:00.000','2009-12-15 00:00:00.000'),
(10,1000,'I','Z',300000,'2001-09-01 00:00:00.000','2009-12-05 00:00:00.000')

--

—

SOLUTION – 1


--

SELECT * FROM Employeex x
CROSS APPLY
(
       SELECT TOP 1 * FROM
       (
             SELECT d StartDate, Next EndD , SUM(Op) OVER (ORDER BY d) #Employees FROM 
             (
                    SELECT DISTINCT d , ISNULL(LEAD(d) OVER (ORDER BY d), GETDATE()) Next  , Op FROM 
                    (
                           SELECT HireDate d , 1 Op  FROM Employeex
                           UNION ALL
                           SELECT ISNULL(TerminationDate,GETDATE()) TerminationDate , -1 FROM Employeex
                    )r     
             ) cr
       )e
       WHERE e.EndD BETWEEN x.HireDate AND ISNULL(x.TerminationDate,GETDATE())
       ORDER BY #Employees DESC
)r


--

SOLUTION – 2


--


select * from Employeex e1 cross apply
(
       Select top 1 dateadd(month, e.number ,'20000101') dt, cnt1 cnt1 from (select top 10000 row_number() over (order by (select null)) - 1 number 
       from Master..spt_values a cross join Master..spt_values b) e
       cross apply 
       (
             select count(*) cnt1 from Employeex e1 where dateadd(month, e.number ,'20000101') between e1.HireDate and isnull(e1.TerminationDate, getdate())  
       ) A 
       where dateadd(month, e.number ,'20000101') <= getdate() and dateadd(month, e.number ,'20000101') between e1.HireDate and isnull(e1.TerminationDate,getdate())
       order by cnt1 desc, dateadd(month, e.number ,'20000101')
) 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 Longest Period – II [ SQL Query to Calculate the longest period ( in days ) that the company has gone without hiring or firing any one ] (Solution Time :))

29 Thursday Sep 2016

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

≈ 6 Comments

Tags

SQL Puzzle | The Longest Period – II

This puzzle is simple. Write a SQL Query to Calculate the longest period ( in days ) that the company has gone without hiring or firing any one.

Sample Inputs

EmployeeId	DepartmentId	FirstName	LastName	Salary	HireDate	TerminationDate
1	1000	A	Z	300000.000	2001-09-01 00:00:00.000	2010-01-16 00:00:00.000
2	1000	B	Y	350000.000	2003-03-15 00:00:00.000	NULL
3	1000	C	X	500000.000	2010-01-15 00:00:00.000	2015-12-02 00:00:00.000
4	1000	D	W	400000.000	2012-05-15 00:00:00.000	NULL
5	1000	D	Q	200000.000	2014-04-15 00:00:00.000	2015-12-02 00:00:00.000
6	1000	E	P	300000.000	2015-08-01 00:00:00.000	NULL
7	1000	F	N	600000.000	2015-12-01 00:00:00.000	NULL
8	1000	G	O	700000.000	2015-12-01 00:00:00.000	NULL
9	1000	H	P	1000000.000	2016-01-01 00:00:00.000	NULL

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 Employeex
(
	 EmployeeId BIGINT
	,DepartmentId INT
	,FirstName VARCHAR(1)
	,LastName VARCHAR(1)
	,Salary NUMERIC(10,3)
	,HireDate DATETIME 
	,TerminationDate DATETIME 
)
GO

INSERT INTO Employeex VALUES
(1,1000,'A','Z',300000,'2001-09-01 00:00:00.000','2010-01-16 00:00:00.000'),
(2,1000,'B','Y',350000,'2003-03-15 00:00:00.000',NULL),
(3,1000,'C','X',500000,'2010-01-15 00:00:00.000','2015-12-02 00:00:00.000'),
(4,1000,'D','W',400000,'2012-05-15 00:00:00.000',NULL),
(5,1000,'D','Q',200000,'2014-04-15 00:00:00.000','2015-12-02 00:00:00.000'),
(6,1000,'E','P',300000,'2015-08-01 00:00:00.000',NULL),
(7,1000,'F','N',600000,'2015-12-01 00:00:00.000',NULL),
(8,1000,'G','O',700000,'2015-12-01 00:00:00.000',NULL),
(9,1000,'H','P',1000000,'2016-01-01 00:00:00.000',NULL)
GO

--

—

SOLUTION – 1


--

SELECT MAX(dr) [LongestPeriod] FROM 
(
       SELECT DATEDIFF(d, a, ISNULL(LEAD(a) OVER (ORDER BY a),GETDATE())) dr FROM 
       (
             SELECT HireDate a FROM Employeex UNION ALL SELECT TerminationDate FROM Employeex WHERE TerminationDate IS NOT NULL
       )t
)r


--

SOLUTION – 2


--

SELECT MAX(gap) [LongestPeriod] FROM
(
       SELECT DATEDIFF(D, EmpDate, ISNULL(LEAD(EmpDate) OVER (ORDER BY EmpDate),GETDATE())) as gap FROM (
              SELECT EmpDate FROM Employeex
                      UNPIVOT(EmpDate FOR DateVal IN (HireDate, TerminationDate)) AS U
       )TblEmp 
)TempEmp

--

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 Date Puzzle – II (Solution Time :))

28 Wednesday Sep 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 | The Date Puzzle – II

This puzzle is simple. Here you need to write a query to get minimum and maximum value records.

For details please check out the sample input and the expected output below.

Sample Inputs

Date	value	time
2016-09-20 17:48:23.447	63	17:48:00.0000000
2016-09-20 17:38:08.880	64	17:38:00.0000000
2016-09-20 17:32:03.247	65	17:32:00.0000000
2016-09-20 17:16:23.273	68	17:16:00.0000000
2016-09-20 17:06:27.223	69	17:06:00.0000000
2016-09-20 16:58:27.693	70	16:58:00.0000000
2016-09-20 16:46:27.030	71	16:46:00.0000000
2016-09-20 16:38:19.490	72	16:38:00.0000000
2016-09-20 16:27:07.743	74	16:27:00.0000000
2016-09-20 16:22:19.243	75	16:22:00.0000000
2016-09-20 16:08:24.197	77	16:08:00.0000000
2016-09-20 15:57:02.757	79	15:57:00.0000000
2016-09-20 15:47:23.227	80	15:47:00.0000000
2016-09-20 15:38:23.937	77	15:38:00.0000000
2016-09-20 15:26:22.017	73	15:26:00.0000000
2016-09-20 15:17:23.277	73	15:17:00.0000000
2016-09-20 15:06:36.990	74	15:06:00.0000000
2016-09-20 14:56:42.550	76	14:56:00.0000000
2016-09-20 14:47:07.823	77	14:47:00.0000000
2016-09-20 14:37:23.907	79	14:37:00.0000000
2016-09-20 14:27:38.387	81	14:27:00.0000000
2016-09-20 14:17:23.307	83	14:17:00.0000000
2016-09-20 14:07:23.687	85	14:07:00.0000000
2016-09-20 13:57:08.230	87	13:57:00.0000000
2016-09-20 13:47:23.307	88	13:47:00.0000000
2016-09-20 13:37:23.463	89	13:37:00.0000000
2016-09-20 13:27:44.067	90	13:27:00.0000000
2016-09-20 13:16:41.130	91	13:16:00.0000000
2016-09-20 13:06:22.003	92	13:06:00.0000000
2016-09-20 13:01:33.437	93	13:01:00.0000000
2016-09-20 12:47:23.217	95	12:47:00.0000000
2016-09-20 12:37:04.723	96	12:37:00.0000000

Expected Output

MinDate	MaxDate	Value	Time
9/20/2016 12:37	9/20/2016 17:48	63	5:48:00 PM
9/20/2016 12:37	9/20/2016 17:48	96	12:37:00 PM

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 testDate
(
       [Date] DATETIME
       ,[value] INT
       ,[time] TIME
)
GO
 
INSERT INTO testDate VALUES
('2016-09-20 17:48:23.447',      63      ,'17:48'),
('2016-09-20 17:38:08.880',      64      ,'17:38'),
('2016-09-20 17:32:03.247',      65      ,'17:32'),
('2016-09-20 17:16:23.273',      68      ,'17:16'),
('2016-09-20 17:06:27.223',      69      ,'17:06'),
('2016-09-20 16:58:27.693',      70      ,'16:58'),
('2016-09-20 16:46:27.030',      71      ,'16:46'),
('2016-09-20 16:38:19.490',      72      ,'16:38'),
('2016-09-20 16:27:07.743',      74      ,'16:27'),
('2016-09-20 16:22:19.243',      75      ,'16:22'),
('2016-09-20 16:08:24.197',      77      ,'16:08'),
('2016-09-20 15:57:02.757',      79      ,'15:57'),
('2016-09-20 15:47:23.227',      80      ,'15:47'),
('2016-09-20 15:38:23.937',      77      ,'15:38'),
('2016-09-20 15:26:22.017',      73      ,'15:26'),
('2016-09-20 15:17:23.277',      73      ,'15:17'),
('2016-09-20 15:06:36.990',      74      ,'15:06'),
('2016-09-20 14:56:42.550',      76      ,'14:56'),
('2016-09-20 14:47:07.823',      77      ,'14:47'),
('2016-09-20 14:37:23.907',      79      ,'14:37'),
('2016-09-20 14:27:38.387',      81      ,'14:27'),
('2016-09-20 14:17:23.307',      83      ,'14:17'),
('2016-09-20 14:07:23.687',      85      ,'14:07'),
('2016-09-20 13:57:08.230',      87      ,'13:57'),
('2016-09-20 13:47:23.307',      88      ,'13:47'),
('2016-09-20 13:37:23.463',      89      ,'13:37'),
('2016-09-20 13:27:44.067',      90      ,'13:27'),
('2016-09-20 13:16:41.130',      91      ,'13:16'),
('2016-09-20 13:06:22.003',      92      ,'13:06'),
('2016-09-20 13:01:33.437',      93      ,'13:01'),
('2016-09-20 12:47:23.217',      95      ,'12:47'),
('2016-09-20 12:37:04.723',      96      ,'12:37')
GO


--

—

SOLUTION – 1


--

SELECT * FROM
(
       SELECT DISTINCT CAST(p.[Date] as DATE) [Date] FROM testDate p
)p
CROSS APPLY
(
       SELECT MIN([Date]) MinDate , MAX([Date]) MaxDate FROM testDate t
       WHERE CAST(t.[DATE] AS DATE) = ((p.[DATE]))
)r
CROSS APPLY
(
       SELECT [value],[Time] FROM testDate t
       WHERE (t.[Date] = r.MinDate OR  t.[Date] = r.MaxDate)
)s
 
--

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 | 2 Digit Puzzle [No Reverse Function] (Solution Time :))

27 Tuesday Sep 2016

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

≈ 5 Comments

Tags

SQL Puzzle | 2 Digit Puzzle

Here you need to write a query to get distinct digits from the table. Now here half data has been reversely inserted. e.g. For 21 we have inserted 12. So out of 12 and 21 we need 21 as a single row.

For details please check out the sample input and the expected output below.

Sample Inputs

Id
21
12
34
43
29
92

Expected Output

Id
21
43
92

Rules/Restrictions

The challenge is to do it with T-SQL statements only.
Reverse function is not allowed.
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 [2Digits]
(
	Id INT
)
GO

INSERT INTO [2Digits] VALUES
(21),
(12),
(34),
(43),
(29),
(92)



--

—

SOLUTION – 1


--

SELECT 
	DISTINCT CASE WHEN Id % 10 < (id / 10) THEN (id / 10)*10 + (Id % 10) ELSE (Id % 10 )*10+(id / 10) END Id 
FROM 
[2Digits]

--

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: The Multiple Sums Puzzle

Share this

Share this

Share this

Share this

Share this