如何计算SQL Server中的夜间工作班次? [英] How to calculate the night working shift in SQL server?
问题描述
我正在使用指纹系统以4个班次记录所有员工的IN OUT日志.正常班次(08:00-> 17:00),班次1:(06-> 14:00),班次2: (14:00-> 22:00)班次3:(22:00->明天06:00).我有2个主表:
I'm using Fingerprint system to record the IN OUT log of all employee in 4 shifts .Normal shift(08:00-> 17:00) , Shift 1:(06-> 14:00), Shift 2: (14:00-> 22:00) Shift 3:(22:00-> 06:00 tomorrow) . I have 2 main table :
当我使用左联接时:
select e.Id as EmpID,CAST(PunchTime as DATE)CheckDate,MIN(cast(a.PunchTime as Time))[TimeIN], max(cast(a.PunchTime as Time))[Time_OUT]
from Employee e
left join AttLog a
on a.EnrollNumber=e.EnrollNumber
group by e.Id,CAST(PunchTime as DATE)
以及当我使用内部联接时:
and when I use the inner join :
select e.Id as EmpID,CAST(PunchTime as DATE)CheckDate,MIN(cast(a.PunchTime as Time))[TimeIN], max(cast(a.PunchTime as Time))[Time_OUT]
from Employee e
inner join AttLog a
on a.EnrollNumber=e.EnrollNumber
group by e.Id,CAST(PunchTime as DATE)
然后,您看到使用左联接时,我们得到了包括空时间在内的所有雇员. 当我们使用内部联接时,如果员工在Shift 3中工作(今天22:00至明天06:00),则仅获得Time IN = Time OUT. 所以我的问题是如何计算班次3的进出时间. 而且,如果员工仅打孔IN,那么Time OUT = Time IN,那么在这种情况下如何将time OUT显示为00:00:00. 我想这样输出:
Then , you see when using left join we get all the employee including null time. And when we use inner join we just get the Time IN = Time OUT if the employee working in the Shift 3 (22:00 today to 06:00 tomorrow) . So my question is how to calculate the time IN and OUT of Shift 3. And if the employee punch only IN then Time OUT = Time IN , how can display time OUT as 00:00:00 in that case. I want to output like this:
EmpID CheckDate TimeIN Time_OUT
5 2015-08-19 2015-08-19 07:51:29.000 2015-08-20 07:43:57.000
14 2015-08-19 2015-08-19 06:52:26.000 2015-08-19 00:00:00.000
EmpID 5,正常工作:08:00-> 17:00,但他必须上夜班,因此他必须待在公司直到明天08:00. EmpID 14在正常的班次中工作,但她忘记打孔了. 目前具有上述数据,输出如下:
EmpID 5 with normal working: 08:00->17:00 but he must took night duty so he must stay at the company until 08:00 tomorrow. EmpID 14 work in normal shift but she forgot to punch OUT. At the present with above data , the output like this:
EmpID CheckDate TimeIN Time_OUT
5 2015-08-19 2015-08-19 07:51:29.000 2015-08-19 07:51:29.000
5 2015-08-20 2015-08-20 07:43:57.000 2015-08-20 07:43:57.000
14 2015-08-19 2015-08-19 06:52:26.000 2015-08-19 06:52:26.000
推荐答案
这是一种排序打孔时间的方法,然后通过递归CTE自联接以将时间缝合在一起.像这样的系统很难控制遗漏的打孔等,但我尝试向您展示一种可以通过添加HoursWorked阈值来做到这一点的方法.
Here's an approach that sequences the punch times, and then self-joins with a recursive CTE to stitch times together. It's hard with a system like this to control for missed punches etc., but I tried show you a way you could do it by adding in an HoursWorked threshold.
/* Create some sample Employee punch data to test*/
IF OBJECT_ID('tempdb..#AttLogTest') IS NOT NULL
DROP TABLE #AttLogTest
CREATE TABLE #AttLogTest (EnrollNumber INT NOT NULL, PunchTime DATETIME NOT NULL)
INSERT INTO #AttLogTest (EnrollNumber, PunchTime)
SELECT 10, '2015-08-01 08:01:03' UNION ALL
SELECT 10, '2015-08-02 07:57:35' UNION ALL
SELECT 10, '2015-08-01 16:15:23' UNION ALL
SELECT 10, '2015-08-02 16:17:46' UNION ALL
SELECT 12, '2015-08-01 21:59:31' UNION ALL
SELECT 12, '2015-08-02 05:59:02' UNION ALL
SELECT 12, '2015-08-02 22:02:28' UNION ALL
SELECT 12, '2015-08-03 06:01:24' UNION ALL
SELECT 14, '2015-08-01 07:59:01' UNION ALL
SELECT 14, '2015-08-02 07:58:16' UNION ALL
SELECT 14, '2015-08-02 16:02:48'
/* Employee time query logic below */
/* First, create a temp table that sequences the punch times for each employee */
IF OBJECT_ID('tempdb..#EmployeeTimeSequence') IS NOT NULL
DROP TABLE #EmployeeTimeSequence
SELECT
EnrollNumber
,PunchTime
,PunchSequence = ROW_NUMBER() OVER(PARTITION BY EnrollNumber ORDER BY PunchTime)
INTO #EmployeeTimeSequence
FROM #AttLogTest --Replace this with your dbo.AttLog table if this solution works for you
/*WHERE clause could be added here to filter for specific dates or EnrollNumbers */
/* If time between punches is greater than this threashold, then it will be treated as a missed punch
in logic below. Remove this or modify as needed. */
DECLARE @MissedPunchThreshold int
SET @MissedPunchThreshold = 20
/* Next, create a recursive CTE which will stitch together the punch times and ensure punch times don't overlap when
self-joining to #EmployeeTimeSequence. */
;WITH EmployeeTimeCTE (EnrollNumber, CheckDate, Time_In, Time_Out, HoursBetweenPunch, PunchOutSequence)
AS (
/* Anchor member */
SELECT
ETS_In.EnrollNumber
,CAST(ETS_In.PunchTime AS DATE) AS CheckDate
,ETS_In.PunchTime AS Time_In
,ETS_Out.PunchTime AS Time_Out
,DateDiff(hour, ETS_In.PunchTime, ETS_Out.PunchTime) AS HoursBetweenPunch
,ETS_Out.PunchSequence AS PunchOutSequence
FROM #EmployeeTimeSequence AS ETS_In
LEFT OUTER JOIN #EmployeeTimeSequence AS ETS_Out
ON ETS_In.EnrollNumber = ETS_Out.EnrollNumber
AND ETS_Out.PunchSequence = ETS_In.PunchSequence + 1
WHERE ETS_In.PunchSequence = 1
UNION ALL
/* Recursive memebr - build on top of anchor */
SELECT
ETS_In.EnrollNumber
,CAST(ETS_In.PunchTime AS DATE) AS CheckDate
,ETS_In.PunchTime AS Time_In
,ETS_Out.PunchTime AS Time_Out
,DateDiff(hour, ETS_In.PunchTime, ETS_Out.PunchTime) AS HoursBetweenPunch
,ETS_Out.PunchSequence AS PunchOutSequence
FROM #EmployeeTimeSequence AS ETS_In --get the time for the in punch
INNER JOIN EmployeeTimeCTE ET
ON ET.EnrollNumber = ETS_In.EnrollNumber
AND ETS_In.PunchSequence =
CASE
WHEN ET.HoursBetweenPunch > @MissedPunchThreshold -- if more than threshold, then treat as missed punch
THEN ET.PunchOutSequence -- then treat the previous out punch as the next in punch instead
ELSE ET.PunchOutSequence + 1 -- else join as usual to get the next punch in sequence
END
INNER JOIN #EmployeeTimeSequence AS ETS_Out -- get the time for the out punch
ON ETS_In.EnrollNumber = ETS_Out.EnrollNumber
AND ETS_Out.PunchSequence = ETS_In.PunchSequence + 1
)
/* Now query the CTE */
SELECT
EnrollNumber AS EmpID
,CheckDate
,Time_In
,CASE WHEN HoursBetweenPunch > @MissedPunchThreshold THEN NULL ELSE Time_Out END AS Time_Out
,CASE WHEN HoursBetweenPunch > @MissedPunchThreshold THEN NULL ELSE HoursBetweenPunch END AS HoursBetweenPunch
FROM EmployeeTimeCTE
ORDER BY EnrollNumber, CheckDate
OPTION (MAXRECURSION 1000)
这篇关于如何计算SQL Server中的夜间工作班次?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!