Dominic

Here's another way of doing it. It avoids the correlated subquery in David's solution and so it may perform better. You'll want to test.

John

-- Data

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

)

-- Number the rows where a code is specified so we can see where it changes

, Numbered (ClaimKey, ARevDate, ARowNum, CauseCode, RowNo) AS (

SELECT ClaimKey, ARevDate, ARowNum, CauseCode,

ROW_NUMBER() OVER (ORDER BY ARowNum)

FROM MyCTE

WHERE CauseCode IS NOT NULL

)

-- Self-join to get ranges with the same code

, ShowNext (ClaimKey, ARevDate, ARowNum, CauseCode, NextRowNum) AS (

SELECT n1.ClaimKey, n1.ARevDate, n1.ARowNum, n1.CauseCode,

COALESCE(n2.ARowNum, (SELECT MAX(ARowNum)+1 FROM MyCTE)) -- add 1 to the highest value for the final number in the range

FROM Numbered n1

LEFT JOIN Numbered n2 -- need LEFT JOIN to capture the final value

ON n1.RowNo = n2.RowNo - 1

)

SELECT m.ClaimKey, m.ARevDate, m.ARowNum,

COALESCE(s.CauseCode,m.CauseCode) CauseCode

FROM MyCTE m

LEFT JOIN ShowNext s

ON m.ARowNum BETWEEN s.ARowNum + 1 AND s.NextRowNum - 1