sql server 统计连续 请假 超过N天的问题

在做EHR项目中，我们会遇到这样类似的需求：

1：统计连续加班或者请假超过7天的人员名单。

2：统计连续排班超过X天的员工。

此类问题的关键在于“连续”

下面拿问题1 来举个实例（此类问题同一原理）：

if object_id('TempDB.dbo.#Test1') is not null 
	DROP table #Test1
go
create table #Test1 (工号 int,日期 datetime)
insert into #Test1
select 10729,'2017-02-01' union all
select 10729,'2017-02-02' union all
select 10729,'2017-02-03' union all
select 10729,'2017-02-05' union ALL
select 10729,'2017-02-06' union ALL
select 10729,'2017-02-07' union ALL
select 10729,'2017-02-08' union all
select 10729,'2017-02-09' union ALL
select 10729,'2017-02-10' union ALL
select 10729,'2017-02-11' union ALL
select 10729,'2017-02-12' union ALL
select 20321,'2017-02-08' union ALL
select 20321,'2017-02-09' union ALL
select 20321,'2017-02-10'

;with TT
as(
select 工号,日期,datediff(dd,ROW_NUMBER() over (partition by 工号 order by 日期),日期) as nn
from TB ),

T1
as(
select *,ROW_NUMBER () over(partition by 工号,nn order by getdate())  as N from TT )

SELECT CONCAT(工号,'从',MIN(CONVERT(VARCHAR(10),T1.日期,120)),'到',MAX(CONVERT(VARCHAR(10),T1.日期,120)),'连续请假',COUNT(1),'天')
	FROM T1 
	GROUP BY 工号,T1.nn
	HAVING COUNT(N)>=7