基于位值的多个日期范围

Question

我有下表的数据

id departmentid  ischanged date
3   22             0      2014-01-04
3   101            0      2014-01-05
3   125            1      2014-01-06
3   169            1      2014-01-07
3   175            0      2014-01-08
3   176            0      2014-01-09
3   177            0      2014-01-10
5   22             0      2014-01-04
5   101            0      2014-01-05
5   125            0      2014-01-06
5   169            0      2014-01-07
5   175            0      2014-01-08
5   176            0      2014-01-09
5   177            0      2014-01-10

我目前的查询是

insert into #temp1(id, startdate, enddate)
SELECT t1.id, '2014-1-4' as startdate, min(isnull(enddate,'2014-01-10')) as endDate
FROM (
            SELECT id, departmentid, ischanged
            FROM dbo.[table]    where date = '2014-1-4'
      ) AS t1
        left join 
        (
            SELECT id, departmentid, ischange , date
            FROM dbo.[table]    where date >= '2014-1-4'
        ) as t2
         on t1.id = t2.id and (t1.ischange <> t2.ischange)
group by t1.id

并将产生以下输出，如果ischange没有更改，则它将从查询中获取硬编码的结束日期，否则它将获取最小的ischange值更改日期

id  startdate      enddate
3   2014-01-04    2014-01-05
5   2014-01-04    2014-01-10

但是我在找一个像这样的结果集

id  startdate      enddate
3   2014-01-04    2014-01-05
3   2014-01-08    2014-01-10
5   2014-01-04    2014-01-10

Answer 1

您需要ischanged为0的句点。您可以使用row_number():的差异来完成此操作

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 = 0
group by id, grp;