>>;with cteRanges as (select *, row_number() over (partition by ClientID order by DateCol desc) as RowNumber, >> >> row_number() over (partition by ClientID order by DateCol desc) - row_number() over(partition by ClientID order by DateCol desc, >>case when status = 7 then 1 else 2 end) as Grp from Invoices) >> >>select * from cteRanges where Grp = 0 and RowNumber >=2>
select * from cteRanges where Grp = 0 and RowNumber >=2 and Status = 7The idea is to mark consecutive ranges of the particular status.