我在员工考勤计算中用到的一些视图和SQL

    p.number between 0 and  DATEDIFF(dd,a.BeginTime,a.EndTime)

用来产生分行记录&＃xff08;有请假段和出差段等的处理&＃xff09;

declare &＃64;sql varchar(8000)
set &＃64;sql &＃61; &＃39; SELECT [EmploId],[EmploName],[Dept],[BeginTime],[EndTime] &＃39;
select &＃64;sql &＃61; &＃64;sql &＃43; &＃39; , max(case [LeaveReson] when &＃39;&＃39;&＃39; &＃43; [LeaveReson] &＃43; &＃39;&＃39;&＃39; then isnull([Hours],7) else 0 end) [&＃39; &＃43; [LeaveReson] &＃43; &＃39;]&＃39;
from (select distinct [LeaveReson] from [tbLeaveRecord]) as a
set &＃64;sql &＃61; &＃64;sql &＃43; &＃39;  from [tbLeaveRecord] where [Year] &＃61; 2010 and [Month] &＃61; 10 group by [EmploId],[EmploName],[Dept],[BeginTime],[EndTime]&＃39;
exec(&＃64;sql)

一个动态的行列转置&＃xff08;主要是对各类请假分类汇总&＃xff09;

 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
)

获取打卡记录的最早最晚时间&＃xff08;这里我提前提取该月打卡记录到了临时表&＃xff09;

 /****** Script for SelectTopNRows command from SSMS  ******/
  --4个时间点切割时间轴为5段&＃xff0c;投下2个时间点&＃xff0c;一种有5&＃43;4&＃43;3&＃43;2&＃43;1 &＃61;15种可能性
  CREATE VIEW VI_OnWorkingTime
  AS
  (
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,0 AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] <&＃39;8:45&＃39; AND [OffWorkTime]<&＃39;8:45&＃39;)--(1)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,&＃39;8:45&＃39;,[OffWorkTime]) AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE [OnWorkTime] <&＃39;8:45&＃39; AND ([OffWorkTime] BETWEEN &＃39;8:45&＃39; AND &＃39;12:00&＃39;)--(2)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,&＃39;8:45&＃39;,&＃39;12:00&＃39;) AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE [OnWorkTime] <&＃39;8:45&＃39; AND ([OffWorkTime] BETWEEN &＃39;12:00&＃39; AND &＃39;13:00&＃39;) AND ([OffWorkTime] <> &＃39;12:00&＃39;)--(3)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,WorkTime &＃61; (DATEDIFF(MINUTE,&＃39;8:45&＃39;,[OffWorkTime])- 60)
  FROM [VI_CheckSystemData]
  WHERE [OnWorkTime] <&＃39;8:45&＃39; AND ([OffWorkTime] BETWEEN &＃39;13:00&＃39; AND &＃39;17:30&＃39;) AND ([OffWorkTime] <> &＃39;13:00&＃39;)--(4)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,7.5 * 60 AS  WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] <&＃39;8:45&＃39;) AND ([OffWorkTime] > &＃39;17:30&＃39;)--(5)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;8:45&＃39; AND &＃39;12:00&＃39;) AND ([OffWorkTime] BETWEEN &＃39;8:45&＃39; AND &＃39;12:00&＃39;)--(6)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,[OnWorkTime],&＃39;12:00&＃39;) AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;8:45&＃39; AND &＃39;12:00&＃39;) AND ([OffWorkTime] BETWEEN &＃39;12:00&＃39; AND &＃39;13:00&＃39;) AND ([OffWorkTime] <> &＃39;12:00&＃39;)--(7)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,WorkTime &＃61; (DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) - 60)
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;8:45&＃39; AND &＃39;12:00&＃39;) AND ([OffWorkTime] BETWEEN &＃39;13:00&＃39; AND &＃39;17:30&＃39;) AND ([OffWorkTime] <> &＃39;13:00&＃39;)--(8)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,WorkTime &＃61; (DATEDIFF(MINUTE,[OnWorkTime],&＃39;17:30&＃39;) - 60)
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;8:45&＃39; AND &＃39;12:00&＃39;) AND ([OffWorkTime] > &＃39;17:30&＃39;)--(9)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,0 AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;12:00&＃39; AND &＃39;13:00&＃39;) AND ([OffWorkTime] BETWEEN &＃39;12:00&＃39; AND &＃39;13:00&＃39;)--(10)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,&＃39;13:00&＃39;,[OffWorkTime]) AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;12:00&＃39; AND &＃39;13:00&＃39;) AND ([OffWorkTime] BETWEEN &＃39;13:00&＃39; AND &＃39;17:30&＃39;) AND ([OffWorkTime] <> &＃39;13:00&＃39;)--(11)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,&＃39;13:00&＃39;,&＃39;17:30&＃39;) AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;12:00&＃39; AND &＃39;13:00&＃39;) AND ([OffWorkTime] > &＃39;17:30&＃39;)--(12)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) as WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;13:00&＃39; AND &＃39;17:30&＃39;) AND ([OffWorkTime] BETWEEN &＃39;13:00&＃39; AND &＃39;17:30&＃39;)--(13)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,[OnWorkTime],&＃39;17:30&＃39;) AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] BETWEEN &＃39;13:00&＃39; AND &＃39;17:30&＃39;) AND ([OffWorkTime] > &＃39;17:30&＃39;)--(14)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,0 AS WorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] > &＃39;17:30&＃39;) AND ([OffWorkTime] > &＃39;17:30&＃39;)--(15)
  )
  这是根据公司考勤规则对上班时间的计算

   CREATE VIEW VI_OverWorkingTime
  AS
  (
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,&＃39;17:30&＃39;,[OffWorkTime]) AS OverWorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] <&＃39;17:30&＃39;) AND ([OffWorkTime] >&＃61; &＃39;17:30&＃39;)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,DATEDIFF(MINUTE,[OnWorkTime],[OffWorkTime]) AS OverWorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] >&＃61; &＃39;17:30&＃39;) AND ([OffWorkTime] >&＃61; &＃39;17:30&＃39;)
  UNION ALL
  SELECT [EmploId]
      ,[EmploName]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,0 AS OverWorkTime
  FROM [VI_CheckSystemData]
  WHERE ([OnWorkTime] <&＃39;17:30&＃39;) AND ([OffWorkTime] <&＃39;17:30&＃39;)
  )

这是根据考勤规则对加班时间的计算

 两部分中是对一般情况的处理&＃xff0c;后期处理需要根据工作日&＃xff08;周一到周五&＃xff09;放假&＃xff0c;非工作日&＃xff08;周六&＃xff0c;周日&＃xff09;上班的对照表再做处理

 Create View VI_CheckFinalResult
AS
SELECT [EmploId]
      ,[EmploName]
      ,[DeptId]
      ,[DeptName]
      ,[LenthOfService]
      ,[IsMid]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,[TW]
      ,[TH]
      ,[TP]
      ,[TN]
      ,CASE WHEN [WorkingTime]>&＃61; 7.5 THEN &＃39;正常考勤&＃39;
            WHEN [WorkingTime]<7.5 AND [WorkingTime]>&＃61;6.5 THEN &＃39;迟到早退&＃39;
            WHEN [WorkingTime]<6.5 AND [WorkingTime]>&＃61;4 THEN &＃39;半天旷工&＃39;
            WHEN [WorkingTime]<4 AND [WorkingTime]>&＃61;0 THEN &＃39;全天旷工&＃39;
            ELSE &＃39;系统异常&＃39;    
       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 &＃61; ([TW]&＃43;ISNULL([TH],0)&＃43;ISNULL([TP],0)&＃43;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] &＃61; &＃39;&＃39;) THEN &＃39;0&＃39; 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] &＃61; 1
) AS CheckObject
LEFT JOIN
(
SELECT [EmploId]
      ,[CheckDay]
      ,[OnWorkTime]
      ,[OffWorkTime]
      ,[WorkTime]
  FROM [tbTempTimeOfWorking]
) AS TW ON CheckObject.[CheckDay] &＃61; TW.[CheckDay] AND CheckObject.[EmploId] &＃61; TW.[EmploId]
LEFT JOIN
(
SELECT [EmploId]
      ,[CheckDay]
      ,[TimeOfLeave]
  FROM [tbTempTimeOfLeave]
)AS TH ON CheckObject.[CheckDay] &＃61; TH.[CheckDay] AND CheckObject.[EmploId] &＃61; TH.[EmploId]
LEFT JOIN
(
SELECT [EmploId]
      ,[CheckDay]
      ,[TimeOfTravel]
  FROM [tbTempTimeOfNonProjectTravelHours]
)AS TP ON CheckObject.[CheckDay] &＃61; TP.[CheckDay] AND CheckObject.[EmploId] &＃61; TP.[EmploId]
LEFT JOIN
(
SELECT [EmploId]
      ,[CheckDay]
      ,[TimeOfTravel]
  FROM [tbTempTimeOfNonProjectTravelHours]
)AS TN ON CheckObject.[CheckDay] &＃61; TN.[CheckDay] AND CheckObject.[EmploId] &＃61; TN.[EmploId]
)

Create View VI_CheckDetailsReport
AS
(
SELECT --请假情况分析
        [EmploId]
       ,[EmploName]
       ,[Dept] AS [DeptName]
       ,[BeginTime]
       ,[EndTime]
       ,[AbsenceOfWork] &＃61; NULL
       ,[LateComeOrEarlyGo] &＃61; NULL
       ,[SickLeave] &＃61; CASE WHEN [LeaveReson] &＃61; &＃39;病假&＃39; AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8)
                           WHEN [LeaveReson] &＃61; &＃39;病假&＃39; AND [CoutSort] &＃61; 0 AND ([Hours] &＃61; &＃39;上午半天&＃39; OR [Hours] &＃61; &＃39;下午半天&＃39;) THEN &＃39;0.5&＃39;
                      END
       ,[SickLeave_Hours] &＃61; CASE WHEN [LeaveReson] &＃61; &＃39;病假&＃39; AND [CoutSort] &＃61; 0 AND ([Hours] <> &＃39;上午半天&＃39; AND [Hours] <> &＃39;下午半天&＃39;) THEN [Hours] END
       ,[AbsenceLeave] &＃61; CASE WHEN [LeaveReson] &＃61; &＃39;事假&＃39; AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8)
                              WHEN [LeaveReson] &＃61; &＃39;事假&＃39; AND [CoutSort] &＃61; 0 AND ([Hours] &＃61; &＃39;上午半天&＃39; OR [Hours] &＃61; &＃39;下午半天&＃39;) THEN &＃39;0.5&＃39;
                      END
       ,[AbsenceLeave_Hours] &＃61; CASE WHEN [LeaveReson] &＃61; &＃39;事假&＃39; AND [CoutSort] &＃61; 0 AND ([Hours] <> &＃39;上午半天&＃39; AND [Hours] <> &＃39;下午半天&＃39;) THEN [Hours] END
       ,[WeddingAndFuneral] &＃61; CASE WHEN ([LeaveReson] &＃61; &＃39;婚假&＃39; OR [LeaveReson] &＃61; &＃39;丧假&＃39;) AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8) END
       ,[HomeLeave] &＃61; CASE WHEN ([LeaveReson] &＃61; &＃39;探父母&＃39; OR [LeaveReson] &＃61; &＃39;探配偶&＃39;) AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8) END
       ,[MaternityAndCare] &＃61; CASE WHEN ([LeaveReson] &＃61; &＃39;产假&＃39; OR [LeaveReson] &＃61; &＃39;护理假&＃39;) AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8) END
       ,[BirthControl] &＃61; CASE WHEN [LeaveReson] &＃61; &＃39;节育假&＃39; AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8) END
       ,[BreastFeedingLeave_Hours] &＃61; CASE WHEN [LeaveReson] &＃61; &＃39;哺乳假&＃39; AND [CoutSort] &＃61; 0    THEN (CASE [Hours] WHEN &＃39;上午半天&＃39; THEN &＃39;0.5&＃39; WHEN &＃39;下午半天&＃39; THEN &＃39;0.5&＃39; ELSE [Hours] END) END
       ,[PaidHoliday] &＃61; CASE WHEN [LeaveReson] &＃61; &＃39;带薪假&＃39; AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8)
                           WHEN [LeaveReson] &＃61; &＃39;带薪假&＃39; AND [CoutSort] &＃61; 0 AND ([Hours] &＃61; &＃39;上午半天&＃39; OR [Hours] &＃61; &＃39;下午半天&＃39;) THEN &＃39;0.5&＃39;
                        END
       ,[Ohters] &＃61;  CASE WHEN [LeaveReson] &＃61; &＃39;其他假&＃39; AND [CoutSort] &＃61; 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)&＃43;1),8)
                         WHEN [LeaveReson] &＃61; &＃39;其他假&＃39; AND [CoutSort] &＃61; 0 AND ([Hours] &＃61; &＃39;上午半天&＃39; OR [Hours] &＃61; &＃39;下午半天&＃39;) THEN &＃39;0.5&＃39;
                         WHEN [LeaveReson] &＃61; &＃39;其他假&＃39; AND [CoutSort] &＃61; 0 AND ([Hours] <> &＃39;上午半天&＃39; OR [Hours] <> &＃39;下午半天&＃39;) THEN [Hours]
                    END
       ,[Bak]             
  FROM [tbTempLeaveRecord]
UNION ALL
SELECT --请假情况分析
        [EmploId]
       ,[EmploName]
       ,[DeptName]
       ,[BeginTime] &＃61; [CheckDay]
       ,[EndTime] &＃61;[CheckDay]
       ,[AbsenceOfWork] &＃61; CASE WHEN [Result] &＃61; &＃39;全天旷工&＃39; THEN &＃39;1&＃39;
                               WHEN [Result] &＃61; &＃39;半天旷工&＃39; THEN &＃39;0.5&＃39;
                          END
       ,[LateComeOrEarlyGo] &＃61; CASE WHEN [Result] &＃61; &＃39;迟到早退&＃39; THEN &＃39;1&＃39;  END
       ,[SickLeave] &＃61; NULL
       ,[SickLeave_Hours] &＃61; NULL
       ,[AbsenceLeave] &＃61; NULL
       ,[AbsenceLeave_Hours] &＃61; NULL
       ,[WeddingAndFuneral] &＃61; NULL
       ,[HomeLeave] &＃61; NULL
       ,[MaternityAndCare] &＃61; NULL
       ,[BirthControl] &＃61; NULL
       ,[BreastFeedingLeave_Hours] &＃61; NULL
       ,[PaidHoliday] &＃61; NULL
       ,[Ohters] &＃61;  NULL
       ,[Bak] &＃61;  NULL            
  FROM [VI_CheckFinalResult] WHERE [Result] <> &＃39;正常考勤&＃39;
)  
GO

这里是更具规则出的考勤结论视图&＃xff0c;里面涉及的工作日非工作日登记表和员工信息表都很简单。里面有个tempcheckday的表式一个月时间向量&＃xff0c;其实该用master中的系统来生产&＃xff0c;由于以前不太会也为了偷懒&＃xff0c;就没有那样做。

整个考勤计算过程在一分钟左右&＃xff08;2千人一个月的考勤&＃xff09;。对了&＃xff0c;上面还有三张表是从网上审批系统抽取出来的&＃xff0c;就是对请假&＃xff0c;出差之类的一个审批结果。