年假假统计

第一步：创建工作日表T_Sys_WorkDate，字段包括MyDate datetime,MyWeek nvarchar(50),IsWork int

第二步：往工作日表插入初始数据（默认周六日为非工作日），可以用自定义函数F_AddWorkDate，插入数据，函数如下：（该函数出于论坛某牛人）

create   function   [dbo].[F_AddWorkDate](@dt1   datetime,@dt2   datetime) 
returns   @re   table(id   int   identity(0,1),dt   datetime) 
as 
begin 
declare   @i   int 
select   @i=datediff(day,@dt1,@dt2)+1 

while   @i> 1 
begin 
insert   into   @re(dt)   select   top   100   @dt1   from   syscolumns 
set   @i=@i-100 
end 
delete   from   @re   where   id> datediff(day,@dt1,@dt2) 

update   @re   set   dt=dateadd(day,id,dt) 
return 
end 

插入语句（时间自定）

Insert T_Sys_WorkDate Select dt,DateName(WEEKDAY,dt),case when DATEPART(WEEKDAY,dt) in(7,1) Then 0 else 1 end From f_AddWorkDate('2012-10-1','2012-12-1')

第三步：统计休假，其中需要调用到自定义函数F_UserAnnualLeave和F_GetWorkDay，F_GetWorkDay作用是：判断休假的时间段内包括多少个工作日，函数如下：

CREATE Function [dbo].[F_GetWorkDay](@dt1 datetime,@dt2 datetime)
returns int
as
begin
	declare @DayCount int
	Select @DayCount=Sum(IsWorkDay) From T_Sys_WorkDate Where datediff(day,@dt1, MyDate)>=0 and datediff(day,MyDate, @dt2)>=0
	return(case when @DayCount is not null Then @DayCount else 0 end)
end

 

F_UserAnnualLeave统计年假代码如下：

 

CREATE FUNCTION [dbo].[F_UserAnnualLeave](@startTime nvarchar(50),@endTime nvarchar(50))
RETURNS TABLE
RETURN(
SELECT     UserName, DeptName, DeptNo, VacationType, VacationDay, SumVacation, VacationDay - SumVacation AS RestVacation
FROM         (SELECT     u.UserName, u.DeptNo, d.DeptName, v.VacationType, 
                                              CASE WHEN u1.WorkAge > 20 THEN 15 
                                              WHEN u1.WorkAge=20 AND (13-MONTH(u.InductionDate))%12>=6 THEN 10+(13-MONTH(u.InductionDate))*5/12+1
                                              WHEN u1.WorkAge=20 AND (13-MONTH(u.InductionDate))%12<6 THEN 10+(13-MONTH(u.InductionDate))*5/12
                                              WHEN u1.WorkAge > 10 THEN 10 
                                              WHEN u1.WorkAge=10 AND (13-MONTH(u.InductionDate))%12>=6 THEN 5+(13-MONTH(u.InductionDate))*5/12+1
                                              WHEN u1.WorkAge=10 AND (13-MONTH(u.InductionDate))%12<6 THEN 5+(13-MONTH(u.InductionDate))*5/12
                                              WHEN u1.WorkAge > 1 THEN 5 
                                              WHEN u1.WorkAge=1 AND (13-MONTH(u.InductionDate))%12>=6 THEN (13-MONTH(u.InductionDate))*5/12 +1
                                              WHEN u1.WorkAge=1 AND (13-MONTH(u.InductionDate))%12<6 THEN (13-MONTH(u.InductionDate))*5/12                                              
                                              ELSE 0 END AS VacationDay, 
                                              SUM(v.VacationDay) AS SumVacation
                       FROM          dbo.T_Sys_UserInfo AS u LEFT OUTER JOIN
                                                  (SELECT     ID, FlowID, Title, UserName, UserNo, StartTime, EndTime, VacationType, Remark, msrepl_tran_version, 
                                                                           CASE WHEN DATEDIFF(HOUR, [StartTime], [EndTime]) % 24 = 0 THEN dbo.F_GetWorkDay([StartTime], [EndTime]) 
                                                                            WHEN DATEDIFF(HOUR, [StartTime], [EndTime]) % 24 >= 9 THEN dbo.F_GetWorkDay([StartTime], [EndTime])
                                                                            ELSE dbo.F_GetWorkDay([StartTime], [EndTime]) - 0.5 END AS VacationDay
                                                    FROM          dbo.T_BGSW_Vacation
                                                    WHERE    StartTime>=@startTime AND EndTime<=@endTime) AS v ON v.UserName = u.UserName LEFT OUTER JOIN
                                                  (SELECT     ID, CASE WHEN DATEDIFF(YEAR, InductionDate, GETDATE()) <= 0 THEN 0 WHEN MONTH(GETDATE()) - MONTH(InductionDate) 
                                                                           > 0 THEN DATEDIFF(YEAR, InductionDate, GETDATE()) WHEN MONTH(GETDATE()) - MONTH(InductionDate) = 0 AND 
                                                                           DAY(GETDATE()) - DAY(InductionDate) >= 0 THEN DATEDIFF(YEAR, InductionDate, GETDATE()) ELSE DATEDIFF(YEAR, 
                                                                           InductionDate, GETDATE()) - 1 END AS WorkAge
                                                    FROM          dbo.T_Sys_UserInfo) AS u1 ON u.ID = u1.ID LEFT OUTER JOIN
                                              dbo.T_Sys_DeptInfo AS d ON d.ID = u.DeptNo JOIN 
                                              [T_BGSW_Main] M on M.FlowID=V.FlowID WHERE  NextNodeID=0 AND M.PrevNodeName='人事备案'
                       GROUP BY u.UserName, u.DeptNo, d.DeptName, v.VacationType, u.InductionDate, u1.WorkAge) AS maintab
WHERE     (VacationType = '年假')