sqlsql-serversql-server-2005t-sqlgaps-and-islands

How do I group on contiguous ranges?


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?


Solution

  • 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: