Multiple Date Ranges for an Employee

Question

I have data like this

id   date           ischanged
1    01-01-2014     0
1    02-01-2014     1
1    03-01-2014     1
1    04-01-2014     1
1    05-01-2014     0
1    06-01-2014     1
1    07-01-2014     0

2    01-01-2014     1
2    02-01-2014     1
2    03-01-2014     1
2    04-01-2014     0
2    05-01-2014     0
2    06-01-2014     1
2    07-01-2014     1

Is it possible to query the data to get the below results from above table based on Ischanged value

id   startdate     enddate
1    02-01-2014     04-01-2014
1    06-01-2014     06-01-2014
2    01-01-2014     03-01-2014
2    06-01-2014     07-01-2014

I tried my query to join the table to self and find the first ischanged value but that will only give me results something like this

id   startdate     enddate
1    02-01-2014     04-01-2014
2    01-01-2014     03-01-2014

Is there a way to get multiple date ranges for the same ID?

Answer 1

This would be a bit easier with the lag() function, but you can still do this in SQL Server 2008.

select id, min(date) as startdate, max(date) as enddate
from (select t.*,
             (row_number() over (partition by id order by date) -
              row_number() over (partition by id, ischanged order by date)
             ) as grp
      from table t 
     ) t
where ischanged = 1
group by id, grp;

Notice that this is using the difference of two row_number()s. The first is a sequential number for all rows for an id (by date). The second is a separate sequential number for an id and ischanged value. When the ischanged values are consecutive, the difference is a constant -- that identifies each group.

The outer query just aggregates by this group.