再改一下:
select *,0 as Ep into #temp from EmpWork
where convert(char(7),workdate,120)='2004-03'
order by empid,workdate
declare @a int,@b int
update #temp set @a=case when @b= empid and worked=1 then @a+1 else 0 end, @b=empid,ep=@a
select distinct empid from #temp where worked=1 and ep=9
select *,0 as Ep into #temp from EmpWork
where convert(char(7),workdate,120)='2004-03'
order by empid,workdate
declare @a int,@b int,@c bit
update #temp
set @a=case when @b= empid and @c=1 then @a+1 else 0 end, @b=empid,@c=worked,ep=@a
select distinct empid from #temp where worked=1 and ep=9
select *,0 as Ep into #temp from EmpWork order by empid,workdate
declare @a int,@b int,@c bit
update #temp set @a=case when @b= empid and @c=1 then @a+1 else 0 end, @b=empid,@c=worked,ep=@a
select distinct empid from #temp where worked=1 and ep=9
其中的关键SQL语句:
Select @WorkDayCount=sum(worked) from EmpWork
where empid=@strEmpID and workdate like @strMonth+'%'
and workdate>=@WorkDate and workdate<convert(varchar(10),Cast(@WorkDate as datetime)+@intDayCount,121)
--此函数判断某员工,在某月,是否连续工作了N天以上
CREATE FUNCTION WorkContinued(
@strEmpID varchar(50),
@strMonth varchar(7),
@intDayCount int)
RETURNS int AS
begin
declare @WorkDate Varchar(10)
declare @WorkDayCount int
declare @Result int
DECLARE Workdate_Cur CURSOR
FOR SELECT workdate FROM EmpWork
where empid=@strEmpID and workdate like @strMonth+'%' and worked=1
order by workdate
set @Result=0
open WorkDate_Cur
FETCH NEXT FROM WorkDate_Cur INTO @WorkDate
while @@Fetch_status=0
begin
Select @WorkDayCount=sum(worked) from EmpWork
where empid=@strEmpID and workdate like @strMonth+'%'
and workdate>=@WorkDate and workdate<convert(varchar(10),Cast(@WorkDate as datetime)+@intDayCount,121)
if @WorkDayCount>=@intDayCount
begin
set @Result=1
break
end
FETCH NEXT FROM WorkDate_Cur INTO @WorkDate
end
close WorkDate_Cur
deallocate WorkDate_Cur
return @Result
End
创建此函数后,使用
select distinct empid from empwork
where dbo.WorkContinued(empid ,'2004-03',10)=1