64.缺勤天数统计的处理案例

红太狼 2024-02-19 18:00 31阅读 0赞

--计算两个日期之间相差的工作天数
    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
    --*/