How do I group on continuous ranges
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 - rnThis article may be of interest to you:
WITH grouped AS ( SELECT *, grp = DATEDIFF(day, 0, Date) - ROW_NUMBER() OVER (PARTITION BY Crew, DayType ORDER BY Date) FROM @testtable)SELECT DateFrom = MIN(Date), DateTo = MAX(Date), Name = Crew, DayTypeFROM groupedGROUP BY Crew, DayType, grp;Basically, same as Quassnoi's solution, but using one ROW_NUMBER fewer yields a better execution plan.
SELECT MIN(Date) AS DateFrom,MAX(Date) AS DateTo, Crew, DayType FROM yourTableName GROUP BY Crew, DayType