mysql统计缺勤的天数_缺勤天数统计的处理示例.sql

--计算两个日期之间相差的工作天数

CREATE FUNCTION f_WorkDateDiff(

@dt_begin datetime,

@dt_end datetime)

RETURNS int

AS

BEGIN

DECLARE @workday int,@i int,@bz bit,@dt datetime

IF @dt_begin>@dt_end

SELECT @bz=1,@dt=@dt_bsegin,@dt_begin=@dt_end,@dt_end=@dt

ELSE

SET @bz=0

SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,

@workday=@i/7*5,

@dt_begin=DATEADD(Day,@i/7*7,@dt_begin)

WHILE @dt_begin<=@dt_end

BEGIN

SELECT @workday=CASE

WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5

THEN @workday+1 ELSE @workday END,

@dt_begin=@dt_begin+1

END

RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)

END

GO

--测试数据

CREATE TABLE tb(Name varchar(10),WorkDate datetime)

INSERT tb SELECT 'aa','2005-01-03'

UNION ALL SELECT 'aa','2005-01-04'

UNION ALL SELECT 'aa','2005-01-05'

UNION ALL SELECT 'aa','2005-01-06'

UNION ALL SELECT 'aa','2005-01-07'

UNION ALL SELECT 'aa','2005-01-10'

UNION ALL SELECT 'aa','2005-01-14'

UNION ALL SELECT 'aa','2005-01-17'

UNION ALL SELECT 'bb','2005-01-11'

UNION ALL SELECT 'bb','2005-01-12'

UNION ALL SELECT 'bb','2005-01-13'

UNION ALL SELECT 'bb','2005-01-10'

UNION ALL SELECT 'bb','2005-01-14'

UNION ALL SELECT 'bb','2005-01-20'

GO

--缺勤统计

DECLARE @dt_begin datetime,@dt_end datetime

SELECT @dt_begin='2005-1-1', --统计的开始日期

@dt_end='2005-1-20'        --统计的结束日期

--统计

SELECT Name,Days=SUM(Days) FROM(

SELECT Name,Days=dbo.f_WorkDateDiff(

DATEADD(Day,1,WorkDate),

ISNULL(DATEADD(Day,-1,(

SELECT MIN(WorkDate) FROM tb aa

WHERE Name=a.Name

AND WorkDate>a.WorkDate AND WorkDate<=@dt_end

AND NOT EXISTS(

SELECT * FROM tb

WHERE WorkDate>@dt_begin AND WorkDate<=@dt_end

AND Name=aa.Name

AND dbo.f_WorkDateDiff(WorkDate,aa.WorkDate)=2))

),@dt_end))

FROM(

SELECT Name,WorkDate FROM tb

WHERE WorkDate>=@dt_begin AND WorkDate

UNION ALL --为每组编号补充查询起始编号是否缺号的辅助记录

SELECT DISTINCT Name,DATEADD(Day,-1,@dt_begin) FROM tb

)a

WHERE (@@DATEFIRST+DATEPART(Weekday,WorkDate)-1)%7 BETWEEN 1 AND 5

AND NOT EXISTS(

SELECT * FROM tb

WHERE WorkDate>@dt_begin AND WorkDate<=@dt_end

AND Name=a.Name

AND dbo.f_WorkDateDiff(WorkDate,a.WorkDate)=-2)

)aa GROUP BY Name

/*--结果

Name       Days

---------------- -----------

aa         6

bb         8

--*/