Just to be clear.

Lowell used the following code as sample data:

;WITH MyCTE([EMPID],[EffectiveDate],[PrimaryRater])

AS

(

SELECT '12345',convert(datetime,'10/10/2001'),'A12345' UNION ALL

SELECT '12345','07/12/2013','A12345' UNION ALL

SELECT '12345','08/18/2002','A12345' UNION ALL

SELECT '12345','07/17/1966','A12345' UNION ALL

SELECT '12345','01/01/1966','B12345'

)

And the following is the actual solution:

SELECT EMPID,

Min(EffectiveDate) AS StartDate,

PrimaryRater,

max(EffectiveDate)

FROM MyCTE

GROUP BY

EMPID,

PrimaryRater

This will work with millions of rows and any combinations. Be sure that PrimaryRater is always the same for each employee, otherwise, you'll get a row for evey EMPID and PrimaryRater combination.