How do I group on continuous ranges
11,781
Solution 1
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, dayType
FROM q
GROUP BY
crew, dayType, rnd - rn
This article may be of interest to you:
Solution 2
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,
DayType
FROM grouped
GROUP BY Crew, DayType, grp;
Basically, same as Quassnoi's solution, but using one ROW_NUMBER
fewer yields a better execution plan.
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Author by
Admin
Updated on July 21, 2022Comments
-
Admin almost 2 years
I know some basic SQL, but this one is beyond me. I have looked high and low but no dice. I need a view of the following data, I can do this in the application layer code. But unfortunately for this particular one, the code must be put in the data layer.
I am using T-SQL.
Table
Date Crew DayType 01-02-11 John Doe SEA 02-02-11 John Doe SEA 03-02-11 John Doe SEA 04-02-11 John Doe HOME 05-02-11 John Doe HOME 06-02-11 John Doe SEA
I need a view like this
DateFrom DateTo Name DayType 01-02-11 03-02-11 John Doe SEA 04-02-11 05-02-11 John Doe HOME 06-02-11 06-02-11 John Doe SEA
Unfortunately, the base table is required for the application layer to be in the format shown. Is this possible to do in a query?