--計算兩個日期之間相差的工作天數
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<@dt_end
??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
--*/

缺勤天數統計的處理示例.sql