>>
>>The simplest solution that comes to mind is this:
>>
>>;with GapsAndIslands as (select *, row_number() over (order by ProcessDate) - ROW_NUMBER() over (partition by ProcessName order by ProcessDate) as Grp
>>from @ProcessRows)
>>
>>select min(ProcessDate) as StartDate, max(ProcessDate) as EndDate,
>>count(*) as NumRows, ProcessName
>>from GapsAndIslands
>>Group by ProcessName, Grp
>>order by StartDate;
>>
>>You may want to also check
https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/>
>Wow - that's better than my solution. I used a combination of RANK() and LAG(), but it took more levels of subqueries. It never occurred to me to subtract row numbers derived from different partitions. I'm impressed! Thanks very much.
You're welcome.
If it's not broken, fix it until it is.
My Blog