>I honestly thought this was going to be an easy one, and wound up spending way more time pulling my hair out than I thought I would.
>
>I have a solution, but it's rather nasty, and I'm curious if anyone wants to take a crack. I'll post my SQL code later, I want to see if anyone comes up with something more elegant. (My solution is not very elegant)
>
>OK, here goes....suppose you have a piece of inventory that's in Phase A for a few days, then in Phase B, then in Phase C, then "back and forth" between A and C and D in some way.
>You want to generate a result set of each Phase and the number of days at the phase, keeping in mind that something could be at Phase A several times in between other phases.
>
>So here's some sample data:
>
>

>declare @ProcessRows table  (ProcessDate Date, ProcessName varchar(50)) 
>
>insert into @ProcessRows values ('10/1/2017', 'Process A') 
>insert into @ProcessRows values ('10/2/2017', 'Process A') 
>insert into @ProcessRows values ('10/3/2017', 'Process A') 
>
>insert into @ProcessRows values ('10/4/2017', 'Process B') 
>insert into @ProcessRows values ('10/5/2017', 'Process B') 
>insert into @ProcessRows values ('10/6/2017', 'Process B') 
>insert into @ProcessRows values ('10/7/2017', 'Process B') 
>
>insert into @ProcessRows values ('10/8/2017', 'Process C') 
>
>insert into @ProcessRows values ('10/9/2017', 'Process A')   
>insert into @ProcessRows values ('10/10/2017', 'Process A')   
>insert into @ProcessRows values ('10/11/2017', 'Process A') 
>    
>insert into @ProcessRows values ('10/12/2017', 'Process D') 
>insert into @ProcessRows values ('10/13/2017', 'Process D') 
>
>insert into @ProcessRows values ('10/14/2017', 'Process A') 
>insert into @ProcessRows values ('10/15/2017', 'Process A')   
>
>insert into @ProcessRows values ('10/16/2017', 'Process D')   
>

>
>I want to produce the following result set:
>
>processname       FirstDate      NumRows
>Process A       2017-10-01        3
>Process B        2017-10-04        4
>Process C        2017-10-08        1
>Process A        2017-10-09        3
>Process D        2017-10-12        2
>Process A        2017-10-14        2
>Process D        2017-10-16        1
>
>Any takers? :)
>
>One stipulation: it can't use a cursor. In production there would be far too much data.
>
>Thanks

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/