Hi iammann,
Please refer below SQL Query
DECLARE @PhoneLog AS TABLE(ID INT,FromPhoneNumber Varchar(100),ToPhoneNumber Varchar(100),Starttime DateTime,EndTime DateTime)
INSERT INTO @PhoneLog Values(101,9878650055,9878650002,'2016-10-09 12:17:48.483','2016-10-09 12:20:48.483')
INSERT INTO @PhoneLog Values(101,9878650055,9878650000,'2016-10-09 13:17:48.483','2016-10-09 13:20:48.483')
INSERT INTO @PhoneLog Values(101,9878650055,9878650008,'2016-10-09 14:17:48.483','2016-10-09 14:20:48.483')
INSERT INTO @PhoneLog Values(101,9878650055,9878650000,'2016-10-09 15:17:48.483','2016-10-09 15:20:48.483')
INSERT INTO @PhoneLog Values(101,9878650055,9878650002,'2016-10-09 20:17:48.483','2016-10-09 20:20:48.483')
INSERT INTO @PhoneLog Values(102,9878650051,9878650099,'2016-10-09 12:17:48.483','2016-10-09 12:20:48.483')
INSERT INTO @PhoneLog Values(102,9878650051,9878650099,'2016-10-09 12:21:48.483','2016-10-09 12:25:48.483')
INSERT INTO @PhoneLog Values(102,9878650051,9878650099,'2016-10-09 12:29:48.483','2016-10-09 12:31:48.483')
INSERT INTO @PhoneLog Values(102,9878650051,9878650077,'2016-10-09 20:29:48.483','2016-10-09 20:35:48.483')
INSERT INTO @PhoneLog Values(103,9878650052,9878650098,'2016-10-10 12:17:48.483','2016-10-09 12:20:48.483')
INSERT INTO @PhoneLog Values(103,9878650052,9878650099,'2016-10-10 12:21:48.483','2016-10-09 12:25:48.483')
INSERT INTO @PhoneLog Values(103,9878650052,9878650099,'2016-10-10 12:29:48.483','2016-10-09 12:31:48.483')
INSERT INTO @PhoneLog Values(103,9878650052,9878650098,'2016-10-10 20:29:48.483','2016-10-09 20:35:48.483')
DECLARE @Result AS TABLE(ID INT,FromPhoneNumber Varchar(100),ToPhoneNumber Varchar(100),Starttime DateTime,EndTime DateTime)
DECLARE @Ids INT SET @Ids = (SELECT TOP 1 ID FROM @PhoneLog ORDER BY ID)
DECLARE @Count INT SET @Count = (SELECT COUNT(DISTINCT ID) FROM @PhoneLog)
DECLARE @i INT SET @i = 1
WHILE (@i <= @Count)
BEGIN
DECLARE @First VARCHAR(10) SET @First = (SELECT TOP 1 ToPhoneNumber FROM @PhoneLog WHERE ID = @Ids ORDER BY Starttime ASC)
DECLARE @Last VARCHAR(10) SET @Last = (SELECT TOP 1 ToPhoneNumber FROM @PhoneLog WHERE ID = @Ids ORDER BY Starttime DESC)
IF(@First = @Last)
BEGIN
INSERT INTO @Result SELECT TOP 1 * FROM @PhoneLog WHERE ID = @Ids AND ToPhoneNumber = @Last
END
SET @Ids = @Ids + 1
SET @i = @i + 1
END
SELECT ID,FromPhoneNumber,ToPhoneNumber,CONVERT(VARCHAR(10),Starttime,105)[Date] FROM @Result
OUTPUT
| ID |
FromPhoneNumber |
ToPhoneNumber |
Date |
| 101 |
9878650055 |
9878650002 |
9/10/2016 |
| 103 |
9878650052 |
9878650098 |
10/10/2016 |
