As to me, it would be 17 rows in output, because ZIP code:
55407
is reapeated 15 times and other ZIP codes are repeated twice.
See:
SET DATEFORMAT mdy;
DECLARE @tmp TABLE (ID INT, ZIP INT, [START] Date, [END] Date)
INSERT INTO @tmp(ID, ZIP, [START], [END])
VALUES(500, 55106, '7/8/2009', '9/1/2009'),
(500, 55407, '9/2/2009', '11/23/2009'),
(500, 55411, '11/24/2009', '11/29/2009'),
(500, 55407, '11/30/2009', '12/13/2009'),
(500, 55429, '12/14/2009', '12/20/2009'),
(500, 55407, '12/21/2009', '12/22/2009'),
(500, 55407, '12/23/2009', '1/3/2010'),
(500, 55407, '1/4/2010', '1/10/2010'),
(500, 55304, '1/11/2010', '1/13/2010'),
(500, 55407, '1/14/2010', '1/25/2010'),
(500, 55121, '1/26/2010', '2/9/2010'),
(500, 55119, '2/10/2010', '2/10/2010'),
(500, 55428, '2/11/2010', '2/11/2010'),
(500, 55038, '2/12/2010', '2/17/2010'),
(500, 55433, '2/18/2010', '3/8/2010'),
(500, 55406, '3/9/2010', '3/22/2010'),
(500, 55406, '3/23/2010', '3/24/2010'),
(500, 55433, '3/25/2010', '4/22/2010'),
(500, 55415, '4/23/2010', '6/20/2013'),
(500, 55106, '6/21/2013', '9/15/2013'),
(500, 55407, '9/16/2013', '9/19/2013'),
(500, 55407, '9/20/2013', '10/16/2013'),
(500, 55407, '10/17/2013', '11/25/2013'),
(500, 55102, '11/26/2013', '12/12/2013'),
(500, 55130, '12/13/2013', '1/21/2014'),
(500, 55407, '1/22/2014', '2/16/2014'),
(500, 55407, '2/17/2014', '7/10/2014'),
(500, 55125, '7/11/2014', '7/28/2014'),
(500, 55407, '7/29/2014', '10/29/2014'),
(500, 55411, '10/30/2014', '12/2/2014'),
(500, 55407, '12/3/2014', '7/7/2015'),
(500, 55434, '7/8/2015', '8/24/2015'),
(500, 55434, '8/25/2015', '11/3/2015'),
(500, 55130, '11/4/2015', '8/18/2016'),
(500, 55407, '8/19/2016', '7/11/2018'),
(500, 55407, '7/12/2018', '10/1/2018'),
(500, 55408, '10/2/2018', '10/5/2018'),
(500, 55411, '10/6/2018', '12/30/9999')
UPDATE t1 SET [START] = t2.MinStart,
[END] = t2.MaxEnd
FROM @tmp t1 INNER JOIN
(
SELECT ID, ZIP, MIN([START]) OVER(PARTITION BY ZIP ORDER BY [START]) AS MinStart, MAX([END]) OVER(PARTITION BY ZIP ORDER BY [END] DESC) AS MaxEnd
FROM @tmp
) t2 ON t1.ID = t2.ID AND t1.ZIP = t2.ZIP
SELECT ID, ZIP, [START], [END], RowNo
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY ID, ZIP ORDER BY [START]) AS RowNo
FROM @tmp
) T
Result:
ID ZIP START END RowNo
500 55038 2010-02-12 2010-02-17 1
500 55102 2013-11-26 2013-12-12 1
500 55106 2009-07-08 2013-09-15 1
500 55106 2009-07-08 2013-09-15 2
500 55119 2010-02-10 2010-02-10 1
500 55121 2010-01-26 2010-02-09 1
500 55125 2014-07-11 2014-07-28 1
500 55130 2013-12-13 2016-08-18 1
500 55130 2013-12-13 2016-08-18 2
500 55304 2010-01-11 2010-01-13 1
500 55406 2010-03-09 2010-03-24 1
500 55406 2010-03-09 2010-03-24 2
500 55407 2009-09-02 2018-10-01 1
500 55407 2009-09-02 2018-10-01 2
500 55407 2009-09-02 2018-10-01 3
500 55407 2009-09-02 2018-10-01 4
500 55407 2009-09-02 2018-10-01 5
500 55407 2009-09-02 2018-10-01 6
500 55407 2009-09-02 2018-10-01 7
500 55407 2009-09-02 2018-10-01 8
500 55407 2009-09-02 2018-10-01 9
500 55407 2009-09-02 2018-10-01 10
500 55407 2009-09-02 2018-10-01 11
500 55407 2009-09-02 2018-10-01 12
500 55407 2009-09-02 2018-10-01 13
500 55407 2009-09-02 2018-10-01 14
500 55407 2009-09-02 2018-10-01 15
500 55408 2018-10-02 2018-10-05 1
500 55411 2009-11-24 9999-12-30 1
500 55411 2009-11-24 9999-12-30 2
500 55411 2009-11-24 9999-12-30 3
500 55415 2010-04-23 2013-06-20 1
500 55428 2010-02-11 2010-02-11 1
500 55429 2009-12-14 2009-12-20 1
500 55433 2010-02-18 2010-04-22 1
500 55433 2010-02-18 2010-04-22 2
500 55434 2015-07-08 2015-11-03 1
500 55434 2015-07-08 2015-11-03 2