Some views and SQL I used in employee attendance calculation

SELECT [EmploId]
,[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)

used to generate branches Record （the processing of leaves and business trips ）

declare @sql varchar(8000)
set @sql = & #39; 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] &#61 ; 10 group by [EmploId],[EmploName],[Dept],[BeginTime],[EndTime]'
exec(@sql)

A dynamic row and column Transpose （Mainly classify and summarize all kinds of leave ）

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
)

Get the earliest and latest time of check-in record （Here I extract the punch-in record of the month in advance and put it in the temporary table）

/****** Script for SelectTopNRows command from SSMS******/
–4 time points cut the time axis into 5 segments ， cast 2 time points ， one with 5+4+3+2+1 & #61; 15 possibilities
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] <& ＃39;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] '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&#39 😉 AND ([OffWorkTime] '12:00')–(7)
& # 39; Leave of absence & # 39; AND [CoutSort] & # 61; 0 AND ([Hours] ' Half day in the morning' AND [Hours] ' Half day in the afternoon&#39 😉 THEN [Hours] END
,[WeddingAndFuneral] = CASE WHEN ([LeaveReson] = 'Marriage Leave' OR [LeaveReson] = ' Bereavement leave’) AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8) END
,[HomeLeave] + 61; CASE WHEN ([LeaveReson] = ' Visit Parents' OR [LeaveReson] = THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8) END
,[MaternityAndCare] = CASE WHEN ([LeaveReson] = ' Maternity Leave ‘OR [LeaveReson] = ;1),8) END
,[BirthControl] = CASE WHEN [LeaveReson] = ' Birth Control Leave' AND [CoutSort] = 1 THEN CONVERT( VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8) END
  ,[BreastFeedingLeave_Hours] = CASE WHEN [LeaveReson] = 'Breastfeeding Leave+# 39; AND [CoutSort] = 0 THEN (CASE [Hours] WHEN ' half day in the morning' THEN '0.5' WHEN ' half day in the afternoon' THEN '0.5' ELSE [Hours] END) END
,[PaidHoliday] = CASE WHEN [LeaveReson] = 'Paid Leave' AND [ CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8)
WHEN [LeaveReson] = 'Paid Leave& #39; AND [CoutSort] = 0 AND ([Hours] = ' half day in the morning' OR [Hours] = ' half day in the afternoon') THEN '0.5'
END
,[Ohters] = CASE WHEN [LeaveReson] = 'Other False' AND [CoutSort] = 1 THEN CONVERT(VARCHAR,(DATEDIFF(DAY,BeginTime,EndTime)+1),8)
WHEN [LeaveReson] = 'Other false' AND [CoutSort] = 0 AND ([Hours] = ' half day in the morning' OR [Hours] = ' half day in the afternoon') THEN ' 39;0.5'
WHEN [LeaveReson] = 'Other False' AND [CoutSort] = 0 AND ([Hours] ' AM Half day & # 39; OR [Hours] & # 39; Afternoon half day & # 39;) THEN [Hours]
END
,[Bak]
FROM [tbTempLeaveRecord]
UNION ALL
SELECT –analysis of leave situation
[EmploId]
,[EmploName]
,[DeptName]
,[BeginTime] = [CheckDay ]
,[EndTime] =[CheckDay]
,[AbsenceOfWork] = CASE WHEN [Result] = ' Absence from work all day' THEN & ＃39;1'
WHEN [Result] = ,[ LateComeOrEarlyGo] = CASE WHEN [Result] = /> ,[SickLeave_Hours] = NULL
,[AbsenceLeave] = NULL
,[AbsenceLeave_Hours] = NULL
,[WeddingAndFuneral] = NULL
,[HomeLeave] = NULL
,[MaternityAndCare] = NULL
,[BirthControl] = NULL
,[BreastFeedingLeave_Hours] &#61 ; NULL
,[PaidHoliday] = NULL
,[Ohters] = NULL
,[Bak] = NULL
FROM [VI_CheckFinalResult] WHERE [Result] 'Normal attendance'
)
GO

Here is a more regular attendance conclusion view， involved in it The working day non-working day registration form and the employee information form are very simple. There is a one-month time vector of tempcheckday ， Actually, the system in the master should be used to produce ， because I was not good at it before and I was lazy， so I didn’t do that.

The entire attendance calculation process takes about one minute （2,000 people a month’s attendance）. By the way，there are three other tables above which are extracted from the online approval system，it is an approval result for leave，business trips and the like.

Transfer: https://www.cnblogs.com/wangxiaosmile/archive/2010/10/26/1861226.html