WITH MyCTE(ClaimKey ,ARevDate ,ARowNum,CauseCode) AS

(

SELECT 123456789,20120101 ,1,'Fault'

UNION select 123456789,20120201, 2,NULL

UNION select 123456789,20120301 ,3,NULL

UNION select 123456789,20120401 ,4,NULL

UNION select 123456789,20120501 ,5,'Non-Fault'

UNION select 123456789,20120601 ,6,NULL

UNION select 123456789,20120701 ,7,NULL

UNION select 123456789,20120701 ,8,NULL

UNION SELECT 123456789,20120701 ,9,'Fault'

UNION select 123456789,20120701 ,10,NULL

UNION SELECT 123456789,20120701 ,11,'fault'

UNION SELECT 123456789,20120701 ,12,'non fault'

UNION select 123456789,20120701 ,13,NULL

UNION select 123456789,20120701 ,14,NULL

)

--SELECT TOP 1 [mc1].[CauseCode] FROM [MyCTE] [mc1] WHERE NOT [mc1].[CauseCode] IS NULL AND [mc1].[ARowNum] < 1 ORDER BY [mc1].[ARowNum] DESC

SELECT *

, CASE WHEN [mc].[CauseCode] IS NULL THEN (SELECT TOP 1 [mc1].[CauseCode] FROM [MyCTE] [mc1] WHERE NOT [mc1].[CauseCode] IS NULL AND [mc1].[ARowNum] < [mc].[ARowNum] ORDER BY [mc1].[ARowNum] DESC) ELSE mc.[CauseCode] END AS NewCauseCode

FROM [MyCTE] [mc]