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

Advertisements