Create table #A(PhoneNum varchar(10),PhoneType char(1),PhoneLocation char(1),FileDate date)INSERT INTO #AVALUES ('0000000000','A','B','01/01/2013'),('0000000000','A','B','01/05/2013'),('0000000000','A','B','01/10/2013'),('0000000000','G','B','01/12/2013'),('0000000000','G','B','01/13/2013'),('0000000000','G','B','01/14/2013'),('0000000000','A','B','01/18/2013'),('0000000000','A','B','01/19/2013'),('0000000001','A','V','01/01/2013'),('0000000001','A','V','01/07/2013'),('0000000001','X','Y','01/17/2013'),('0000000001','G','B','01/18/2013'),('0000000001','G','B','01/19/2013'),('0000000001','A','B','01/21/2013')SELECT PhoneNum,PhoneType,PhoneLocation,MIN(FileDate) as StartDate,MAX(FileDate) as EndDateFROM #AGROUP BY PhoneNum,PhoneType,PhoneLocationORDER BY PhoneNum,MIN(FileDate)

PhoneNum PhoneType PhoneLocation StartDate EndDate0000000000 A B 2013-01-01 2013-01-100000000000 G B 2013-01-12 2013-01-140000000000 A B 2013-01-18 2013-01-190000000001 A V 2013-01-01 2013-01-070000000001 X Y 2013-01-17 2013-01-170000000001 G B 2013-01-18 2013-01-190000000001 A B 2013-01-21 2013-01-21

with cte as ( select PhoneNum, PhoneType, PhoneLocation, FileDate ,ROW_NUMBER() OVER (PARTITION BY PhoneNum ORDER BY PhoneType, PhoneLocation, FileDate) - ROW_NUMBER() OVER (PARTITION BY PhoneNum ORDER BY FileDate) R FROM #A )SELECT PhoneNum, PhoneType, PhoneLocation, Min(FileDate), max(FileDate)FROM cteGROUP BY PhoneNum, PhoneType, PhoneLocation, RORDER BY PhoneNum, Min(fileDate);