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:
| 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 to group by DayType such that the result is the following:
| 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?
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: