Find min and max for subsets of consecutive rows - gaps and islands
<p>Trying to build a query.</p>

<p>The input is ordered by rownumber in column 'rn' starting with 1 for each unique value in 'name' and defining a given sequence of entries in 'act'. In column 'act' it holds two values in multiple occurence, >sleep&lt; and >wake&lt;. The goal is to find for each consecutive set of rows of one of those values the minimum and maximum value of startt and endd.</p>

<p>This shall be the input:</p>

<pre><code>name       act        rn     startt endd
---------- ---------- ------ ------ ------
jimmy      sleep      1      1      3
jimmy      wake       2      4      7
jimmy      wake       3      8      10
jimmy      sleep      4      11     13
karen      wake       1      1      4
karen      sleep      2      5      7
karen      wake       3      8      9
karen      wake       4      10     12
karen      wake       5      13     14
karen      sleep      6      15     17
karen      sleep      7      18     20
</code></pre>

<p>the desired output:</p>

<pre><code>name       act        startt endd   
---------- ---------- ------ ------ 
jimmy      sleep      1      3      
jimmy      wake       4      10     
jimmy      sleep      11     13     
karen      wake       1      4      
karen      sleep      5      7      
karen      wake       8      14     
karen      sleep      15     20  
</code></pre>

<p>The source of the input does not provide further columns. The number of members in each subset can be very much higher then in this example.</p>

<p>I tried different ways of aggregating, but none worked. I believe using <code>LEAD</code> and <code>LAGG</code> and further trickery might get me there, but that appears to be awfully unelegant. I have the notion it is key to differentiate each subset, i.e. create an identifier unique to all its members. With this at hand an aggregate with <code>min</code> and <code>max</code> is simple. Maybe i'm wrong. Maybe it's impossible. Maybe a self join. Maybe a recursive cte. I don't know.</p>

<p>So: does anybody know how to get this? Help is much appreciated.</p>

<p><strong>UPDATE:</strong></p>

<p>Thank You to Gordon Linoff, shawnt00 and the other contributors who commented. With Your advice I feel major gaps in my toolbox of logic closing.</p>

<p>For the interested:</p>

<pre><code>declare @t table (
    name nvarchar(10)
    ,act nvarchar (10)
    ,startt smallint
    ,endd smallint
    )

insert into @t (
    name
    ,act
    ,startt
    ,endd
    )
values
     ('jimmy','sleep', 1,3)
    ,('jimmy','wake', 4,7)
    ,('jimmy','wake', 8,10)
    ,('jimmy','sleep', 11,13)
    ,('karen','wake', 1,4)
    ,('karen','sleep', 5,7)
    ,('karen','wake', 8,9)
    ,('karen','wake', 10,12)
    ,('karen','wake', 13,14)
    ,('karen','sleep', 15,17)
    ,('karen','sleep', 18,20)

; --- all rows, no aggregating
with 
cte as (
select 
    name
    ,act
    ,row_number() over (partition by name order by name,startt) rn
    ,row_number() over (partition by name, act order by name,startt) act_n
    ,startt
    ,endd
from
    @t )
select
    name
    ,act
    ,startt
    ,endd
    ,rn
    ,act_n
    ,rn - act_n diff
from 
    cte
order by 
    name
    ,rn

;--- aggregating for the desired ouput
with 
cte as (
select 
    name
    ,act
    ,row_number() over (partition by name order by name,startt) rn
    ,row_number() over (partition by name, act order by name,startt) act_n
    ,startt
    ,endd
from 
    @t )
select
    name
    ,act
    ,min(startt) startt
    ,max(endd)   endd
    ,min(rn)     min_rn
    ,max(rn)     max_rn
from 
    cte
group by 
    name
    ,act
    ,rn - act_n
order by 
    name
    ,min(rn)
</code></pre>