我在员工考勤计算中用到的一些视图和SQL
,[EmploName]
,DATEADD(dd,number,BeginTime) as CheckDay
,[Hours] AS TimeOfTravel
FROM [tbTravel4Project] a,(select distinct number from master..spt_values p) p
where
p.number between 0 and DATEDIFF(dd,a.BeginTime,a.EndTime)
用来产生分行记录（有请假段和出差段等的处理）
declare @sql varchar(8000)
set @sql = ' SELECT [EmploId],[EmploName],[Dept],[BeginTime],[EndTime] '
select @sql = @sql + ' , max(case [LeaveReson] when ''' + [LeaveReson] + ''' then isnull([Hours],7) else 0 end) [' + [LeaveReson] + ']'
from (select distinct [LeaveReson] from [tbLeaveRecord]) as a
set @sql = @sql + ' from [tbLeaveRecord] where [Year] = 2010 and [Month] = 10 group by [EmploId],[EmploName],[Dept],[BeginTime],[EndTime]'
exec(@sql)
一个动态的行列转置（主要是对各类请假分类汇总）
CREATE VIEW VI_CheckSystemData
AS
(
SELECT
[EmploId]
,[EmploName]
,[CheckDay]
,MIN(TheCheckTime) AS OnWorkTime
,MAX(TheCheckTime) AS OffWorkTime
FROM
(
SELECT
EmploId,
EmploName,
CONVERT(DATETIME, CONVERT(varchar, CheckTime, 101)) AS CheckDay,
CONVERT(DATETIME, CONVERT(varchar, CheckTime, 114)) AS TheCheckTime
FROM [tbTempCheckRecord]
) AS n
GROUP BY EmploId,EmploName,CheckDay
)
获取打卡记录的最早最晚时间（这里我提前提取该月打卡记录到了临时表）
/****** Script for SelectTopNRows command from SSMS ******/
--4个时间点切割时间轴为5段，投下2个时间点，一种有5+4+3+2+1 =15种可能性
CREATE VIEW VI_OnWorkingTime
AS
(
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,0 AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] < '8:45' AND [OffWorkTime]< '8:45')--(1)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,'8:45',[OffWorkTime]) AS WorkTime
FROM [VI_CheckSystemData]
WHERE [OnWorkTime] < '8:45' AND ([OffWorkTime] BETWEEN '8:45' AND '12:00')--(2)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,'8:45','12:00') AS WorkTime
FROM [VI_CheckSystemData]
WHERE [OnWorkTime] < '8:45' AND ([OffWorkTime] BETWEEN '12:00' AND '13:00') AND ([OffWorkTime] <> '12:00')--(3)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,WorkTime = (DATEDIFF(MINUTE,'8:45',[OffWorkTime])- 60)
FROM [VI_CheckSystemData]
WHERE [OnWorkTime] < '8:45' AND ([OffWorkTime] BETWEEN '13:00' AND '17:30') AND ([OffWorkTime] <> '13:00')--(4)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,7.5 * 60 AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] < '8:45') AND ([OffWorkTime] > '17:30')--(5)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '8:45' AND '12:00') AND ([OffWorkTime] BETWEEN '8:45' AND '12:00')--(6)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,[OnWorkTime],'12:00') AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '8:45' AND '12:00') AND ([OffWorkTime] BETWEEN '12:00' AND '13:00') AND ([OffWorkTime] <> '12:00')--(7)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,WorkTime = (DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) - 60)
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '8:45' AND '12:00') AND ([OffWorkTime] BETWEEN '13:00' AND '17:30') AND ([OffWorkTime] <> '13:00')--(8)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,WorkTime = (DATEDIFF(MINUTE,[OnWorkTime],'17:30') - 60)
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '8:45' AND '12:00') AND ([OffWorkTime] > '17:30')--(9)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,0 AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '12:00' AND '13:00') AND ([OffWorkTime] BETWEEN '12:00' AND '13:00')--(10)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,'13:00',[OffWorkTime]) AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '12:00' AND '13:00') AND ([OffWorkTime] BETWEEN '13:00' AND '17:30') AND ([OffWorkTime] <> '13:00')--(11)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,'13:00','17:30') AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '12:00' AND '13:00') AND ([OffWorkTime] > '17:30')--(12)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) as WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '13:00' AND '17:30') AND ([OffWorkTime] BETWEEN '13:00' AND '17:30')--(13)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,[OnWorkTime],'17:30') AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] BETWEEN '13:00' AND '17:30') AND ([OffWorkTime] > '17:30')--(14)
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,0 AS WorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] > '17:30') AND ([OffWorkTime] > '17:30')--(15)
)
这是根据公司考勤规则对上班时间的计算
CREATE VIEW VI_OverWorkingTime
AS
(
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,'17:30',[OffWorkTime]) AS OverWorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] < '17:30') AND ([OffWorkTime] >= '17:30')
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) AS OverWorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] >= '17:30') AND ([OffWorkTime] >= '17:30')
UNION ALL
SELECT [EmploId]
,[EmploName]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,0 AS OverWorkTime
FROM [VI_CheckSystemData]
WHERE ([OnWorkTime] < '17:30') AND ([OffWorkTime] < '17:30')
)
这是根据考勤规则对加班时间的计算
两部分中是对一般情况的处理，后期处理需要根据工作日（周一到周五）放假，非工作日（周六，周日）上班的对照表再做处理
Create View VI_CheckFinalResult
AS
SELECT [EmploId]
,[EmploName]
,[DeptId]
,[DeptName]
,[LenthOfService]
,[IsMid]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,[TW]
,[TH]
,[TP]
,[TN]
,CASE WHEN [WorkingTime]>= 7.5 THEN '正常考勤'
WHEN [WorkingTime]<7.5 AND [WorkingTime]>=6.5 THEN '迟到早退'
WHEN [WorkingTime]<6.5 AND [WorkingTime]>=4 THEN '半天旷工'
WHEN [WorkingTime]<4 AND [WorkingTime]>=0 THEN '全天旷工'
ELSE '系统异常'
END AS Result
FROM [VI_CheckTimeResut]
Create View VI_CheckTimeResut
AS
SELECT [EmploId]
,[EmploName]
,[DeptId]
,[DeptName]
,[LenthOfService]
,[IsMid]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,[TW]
,ISNULL([TH],0) AS TH
,ISNULL([TP],0) AS TP
,ISNULL([TN],0) AS TN
,WorkingTime = ([TW]+ISNULL([TH],0)+ISNULL([TP],0)+ISNULL([TN],0))
FROM [VI_CheckResult]
CREATE VIEW VI_CheckResult
AS
(
SELECT
CheckObject.[EmploId]
,CheckObject.[EmploName]
,CheckObject.[DeptId]
,CheckObject.[DeptName]
,CheckObject.[LenthOfService]
,CheckObject.[IsMid]
,CheckObject.[CheckDay]
,TW.[OnWorkTime]
,TW.[OffWorkTime]
,CASE WHEN (TW.[OnWorkTime] IS NULL OR TW.[OnWorkTime] = '') THEN '0' ELSE (TW.[WorkTime]/60.0) END AS TW
,TH.[TimeOfLeave] AS TH
,TP.[TimeOfTravel] AS TP
,TN.[TimeOfTravel] AS TN
FROM
(
SELECT
[tbTempCheckDay].[CheckDay]
,[tbTempEmploInfo].[EmploId]
,[tbTempEmploInfo].[EmploName]
,[tbTempEmploInfo].[DeptId]
,[tbTempEmploInfo].[DeptName]
,[tbTempEmploInfo].[LenthOfService]
,[tbTempEmploInfo].[IsMid]
FROM [tbTempCheckDay] CROSS JOIN [tbTempEmploInfo]
WHERE [tbTempCheckDay].[IsCheckable] = 1
) AS CheckObject
LEFT JOIN
(
SELECT [EmploId]
,[CheckDay]
,[OnWorkTime]
,[OffWorkTime]
,[WorkTime]
FROM [tbTempTimeOfWorking]
) AS TW ON CheckObject.[CheckDay] = TW.[CheckDay] AND CheckObject.[EmploId] = TW.[EmploId]
LEFT JOIN
(
SELECT [EmploId]
,[CheckDay]
,[TimeOfLeave]
FROM [tbTempTimeOfLeave]
)AS TH ON CheckObject.[CheckDay] = TH.[CheckDay] AND CheckObject.[EmploId] = TH.[EmploId]
LEFT JOIN
(
SELECT [EmploId]
,[CheckDay]
,[TimeOfTravel]
FROM [tbTempTimeOfNonProjectTravelHours]
)AS TP ON CheckObject.[CheckDay] = TP.[CheckDay] AND CheckObject.[EmploId] = TP.[EmploId]
LEFT JOIN
(
SELECT [EmploId]
,[CheckDay]
,[TimeOfTravel]
FROM [tbTempTimeOfNonProjectTravelHours]
)AS TN ON CheckObject.[CheckDay] = TN.[CheckDay] AND CheckObject.[EmploId] = TN.[EmploId]
)
Create View VI_CheckDetailsReport
AS
(
SELECT --请假情况分析
[EmploId]
,[EmploName]
,[Dept] AS [DeptName]
,[BeginTime]
,[EndTime]
,[AbsenceOfWork] = NULL
,[LateComeOrEarlyGo] = NULL
,[SickLeave] = CASE WHEN [LeaveReson] = '病假' AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8)
WHEN [LeaveReson] = '病假' AND [CoutSort] = 0 AND ([Hours] = '上午半天' OR [Hours] = '下午半天') THEN '0.5'
END
,[SickLeave_Hours] = CASE WHEN [LeaveReson] = '病假' AND [CoutSort] = 0 AND ([Hours] <> '上午半天' AND [Hours] <> '下午半天') THEN [Hours] END
,[AbsenceLeave] = CASE WHEN [LeaveReson] = '事假' AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8)
WHEN [LeaveReson] = '事假' AND [CoutSort] = 0 AND ([Hours] = '上午半天' OR [Hours] = '下午半天') THEN '0.5'
END
,[AbsenceLeave_Hours] = CASE WHEN [LeaveReson] = '事假' AND [CoutSort] = 0 AND ([Hours] <> '上午半天' AND [Hours] <> '下午半天') THEN [Hours] END
,[WeddingAndFuneral] = CASE WHEN ([LeaveReson] = '婚假' OR [LeaveReson] = '丧假') AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8) END
,[HomeLeave] = CASE WHEN ([LeaveReson] = '探父母' OR [LeaveReson] = '探配偶') AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8) END
,[MaternityAndCare] = CASE WHEN ([LeaveReson] = '产假' OR [LeaveReson] = '护理假') AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8) END
,[BirthControl] = CASE WHEN [LeaveReson] = '节育假' AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8) END
,[BreastFeedingLeave_Hours] = CASE WHEN [LeaveReson] = '哺乳假' AND [CoutSort] = 0 THEN (CASE [Hours] WHEN '上午半天' THEN '0.5' WHEN '下午半天' THEN '0.5' ELSE [Hours] END) END
,[PaidHoliday] = CASE WHEN [LeaveReson] = '带薪假' AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8)
WHEN [LeaveReson] = '带薪假' AND [CoutSort] = 0 AND ([Hours] = '上午半天' OR [Hours] = '下午半天') THEN '0.5'
END
,[Ohters] = CASE WHEN [LeaveReson] = '其他假' AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8)
WHEN [LeaveReson] = '其他假' AND [CoutSort] = 0 AND ([Hours] = '上午半天' OR [Hours] = '下午半天') THEN '0.5'
WHEN [LeaveReson] = '其他假' AND [CoutSort] = 0 AND ([Hours] <> '上午半天' OR [Hours] <> '下午半天') THEN [Hours]
END
,[Bak]
FROM [tbTempLeaveRecord]
UNION ALL
SELECT --请假情况分析
[EmploId]
,[EmploName]
,[DeptName]
,[BeginTime] = [CheckDay]
,[EndTime] =[CheckDay]
,[AbsenceOfWork] = CASE WHEN [Result] = '全天旷工' THEN '1'
WHEN [Result] = '半天旷工' THEN '0.5'
END
,[LateComeOrEarlyGo] = CASE WHEN [Result] = '迟到早退' THEN '1' END
,[SickLeave] = NULL
,[SickLeave_Hours] = NULL
,[AbsenceLeave] = NULL
,[AbsenceLeave_Hours] = NULL
,[WeddingAndFuneral] = NULL
,[HomeLeave] = NULL
,[MaternityAndCare] = NULL
,[BirthControl] = NULL
,[BreastFeedingLeave_Hours] = NULL
,[PaidHoliday] = NULL
,[Ohters] = NULL
,[Bak] = NULL
FROM [VI_CheckFinalResult] WHERE [Result] <> '正常考勤'
)
GO
这里是更具规则出的考勤结论视图，里面涉及的工作日非工作日登记表和员工信息表都很简单。里面有个tempcheckday的表式一个月时间向量，其实该用master中的系统来生产，由于以前不太会也为了偷懒，就没有那样做。
整个考勤计算过程在一分钟左右（2千人一个月的考勤）。对了，上面还有三张表是从网上审批系统抽取出来的，就是对请假，出差之类的一个审批结果。
