I have a table with row ID and Cct numbers. The Cct column can be +1 sequential depending on the preceeding select statement.
I want to find the first row where the next n rows cct no. increments + 1 to n-1.
I think I get it. You are looking for lengths of sequences in the data, where a sequence is incremented by 1.
You can identify them using
row_number() and subtraction -- the difference is fixed for a sequence.
select top 1 min(id), count(*) as length from (select s.*, (cct - row_number() over (order by id)) as grp from sample s ) s group by grp having count(*) >= @n order by min(id)
This gets the first time that a sequence of length (at least)
@n appears in the data.