特定の Act 値に基づいて連続する行の最小値と最大値を見つける方法

row_number() over (partition by name, act order by rn) as act_n
row_number() over (partition by name order by rn) as rn

rn - act_n diff

select name, act, min(startt) startt, max(endd) endd
from (
  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
)
group by (rn - act_n), name, act
order by name;