在做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
sele