第一步:创建工作日表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 = '年假')