I had totally missed the point of the different EndDate for PrimaryRater B12345.

Here's an option:

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

AS

( --This is sample data

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'

),

--Solution starts here

Aggregated AS(

SELECT EMPID,

Min(EffectiveDate) AS StartDate,

PrimaryRater,

max(EffectiveDate) EndDate,

DENSE_RANK() OVER( PARTITION BY EMPID ORDER BY Min(EffectiveDate)) RN

FROM MyCTE

GROUP BY

EMPID,

PrimaryRater

)

SELECT a.EMPID,

a.StartDate,

a.PrimaryRater,

CASE WHEN a.StartDate = a.EndDate THEN b.StartDate ELSE a.EndDate END,

ISNULL( NULLIF(a.StartDate, a.EndDate), b.StartDate)

FROM Aggregated a

LEFT

JOIN Aggregated b ON a.EMPID = b.EMPID AND a.RN = b.RN - 1