How to fetch persons record if first and last row phone no column value is same in SQL Server

Hi iammann,

Will you please provide the expected output from the sample data.

this is expected output

Hi iammann,

What about the record for ID = 102

if there is no cal to first and last cal to same person then record will not show.

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