Multiple date ranges for employee
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 the table above based on the value Ischanged
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 a table to myself and find the first ischanged value, but this will give me results similar to 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?
source to share
It will be a little easier with a function lag()
, but you can still do it 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;
Note that this uses the difference of the two row_number()
s. The first is the sequence number for all rows for id (by date). The second is a separate sequence number for the id value and ischanged
. When the values ischanged
are sequential, the difference is a constant that identifies each group.
The outer query only aggregates this group.
source to share