最近要写一些HR方面的报表，碰到了一个问题，请大神们帮忙看看
一）问题
DEPT NAME ID DATE
0001 小陈 0035 2014-09-01
0001 小陈 0035 2014-09-02
0001 小陈 0035 2014-09-03
0001 小陈 0035 2014-09-04
0001 小陈 0035 2014-09-05
0001 小陈 0035 2014-09-06
0001 小陈 0035 2014-09-08
0001 小陈 0035 2014-09-09
0001 小陈 0035 2014-09-10
0001 小陈 0035 2014-09-11
0001 小陈 0035 2014-09-12
0001 小陈 0035 2014-09-16
0001 小陈 0035 2014-09-17
想让结果：
DEPT NAME ID Begin End
0001 小陈 0035 2014-09-01 2014-09-06
0001 小陈 0035 2014-09-08 2014-09-12
0001 小陈 0035 2014-09-16 2014-09-17
二）这是一个人打卡记录表，有时这个周末双休，有时这个人周末单体，有时这个人周末不休息，我想要的是一个连续的打卡日期 ，从开始日--->结束日
------解决思路----------------------
with cte as
(select '0001' as dept , '小陈'as name,0035 as id,'2014-09-01' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-02' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-03' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-04' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-05' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-06' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-08' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-09' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-10' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-11' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-12' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-16' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-17' as date )
select dept,name,id, Min(date) as begindate,MAX(date) as endtime
from (select *,row_number()over(order by date) as n ,
datediff(day,'2014-08-31',convert(date,[date]))as n1 from cte) as n
group by dept,name,id,n1-n
--结果
dept name id begindate endtime
---- ---- ----------- ---------- ----------
0001 小陈 35 2014-09-01 2014-09-06
0001 小陈 35 2014-09-08 2014-09-12
0001 小陈 35 2014-09-16 2014-09-17
(3 行受影响)
这个是一样的啊。还比我这个例子简单了。
------解决思路----------------------
--借用上面老兄数据
;with cte as
(select '0001' as dept , '小陈'as name,0035 as id,'2014-09-01' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-02' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-03' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-04' as date union all
select '0001' as dept , '小陈'as name,0035 as id,'2014-09-05' as date union all