WITH q AS ( SELECt *, ROW_NUMBER() OVER (PARTITION BY crew, dayType ORDER BY [date]) AS rnd, ROW_NUMBER() OVER (PARTITION BY crew ORDER BY [date]) AS rn FROM mytable )SELECt MIN([date]), MAX([date]), crew AS name, dayTypeFROM qGROUP BY crew, dayType, rnd - rn
