You might be able to use the Quirky update[/url] or the LEAD/LAG functions available on 2012.

However, I did this using an islands solution that might fit your problem.

WITH Groups AS(

SELECT EMP_ID,

Title,

DateValue,

TITLE_YEAR,

DATEADD(YYYY, - ROW_NUMBER() OVER (PARTITION BY EMP_ID, Title ORDER BY DateValue), DateValue) Grouper

FROM #mytable

), Added AS(

SELECT EMP_ID,

Title,

DateValue,

TITLE_YEAR,

ROW_NUMBER() OVER (PARTITION BY EMP_ID, Title, Grouper ORDER BY DateValue) Years

FROM Groups

)

UPDATE Added SET

TITLE_YEAR = Years

SELECT * FROM #mytable