What a really nicely written article to start the day off with, very enjoyable.

Suresh, have you tried using CROSS APPLY to partition the rows into "records" as an alternative to calculating the upper and lower bound? You can then sequence the rows within each "record", then by joining the result to itself (staggered by one row), identification of missing data is quite simple:

;WITH OrderedData AS (

SELECT

Seq_No,

cdata,

x.RowID,

rn = ROW_NUMBER() OVER(PARTITION BY x.RowID ORDER BY p.Seq_No)

FROM #Test_Table p

CROSS APPLY (

SELECT RowID = MAX(Seq_No)

FROM #Test_Table

WHERE CData = '*********************************************' AND Seq_No <= p.Seq_No

) x

)

SELECT t.*

FROM OrderedData t

WHERE t.RowID IN (

SELECT r1.RowID

FROM OrderedData r1

INNER JOIN OrderedData r2

ON r2.RowID = r1.RowID AND r2.rn = r1.rn + 1

WHERE r1.CData = 'RateQuote' AND r2.CData = ' ')

I wonder how this would perform against the original?

Cheers

ChrisM