How do I group on continuous 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.

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 application layer to be in the format show. Is this possible to do in query?

Thanks

Luke

24
задан Martin Smith 7 May 2011 в 21:07
поделиться