从生物识别中获得夜班出勤率 [英] Get night shift attendance from biometric

问题描述

表格转移

table shift

FromTime   ToTime
22:00:00   6:00:00 

表格输入/输出

table in/out

EnrollNumber DateInOut
15045        2016-04-11 22:00:00
15045        2016-04-11 23:00:00   
15045        2016-04-12 06:00:00
15045        2016-04-12 22:00:00
15045        2016-04-13 06:00:00

我要显示

I want to display

EnrollNumber  DateIn              DateOut
15045         2016-04-11 22:00:00 2016-04-12 06:00:00
15045         2016-04-12 22:00:00 2016-04-13 06:00:00

我需要按员工编号显示正确的输入/输出



什么我试过了：

I need to display the correct in/out by employee number

What I have tried:

Select EnrollNumber, the_date,MIN(min_checkin) as TimeIn , MAX(max_checkout) as TimeOut FROM (
SELECT EnrollNumber, CONVERT(char(10), DateInOut, 102) AS the_date, MIN(CAST(DateInOut as time)) AS min_checkin, null as max_checkout
    FROM tblCheckInOut 
    GROUP BY EnrollNumber, CONVERT(char(10), DateInOut, 102)
UNION ALL
SELECT EnrollNumber, CONVERT(char(10), DateInOut, 102) AS the_date, null AS min_checkin, MAX(CAST(DateInOut as time)) as max_checkout
    FROM tblCheckInOut 
    GROUP BY EnrollNumber, CONVERT(char(10), DateInOut, 102)
	) as Result Group By EnrollNumber,the_date

推荐答案

正如我在评论中提到的那样r输入数据不是干净，因为2016-04-11有两个检查时间

As I noted in my comment your input data is not "clean" in that there are two check in times for 2016-04-11

EnrollNumber DateInOut
15045        2016-04-11 22:00:00
15045        2016-04-11 23:00:00   
15045        2016-04-12 06:00:00
15045        2016-04-12 22:00:00
15045        2016-04-13 06:00:00

鉴于您的预期结果，您似乎想要忽略上面突出显示的行。



首先你需要清理你的数据......我已经假设任何时差小于整个班次的四分之一都是异常数据。此查询将生成已清理的信息表：

Given your expected results it would appear that you want to ignore the row highlighted above.

First you need to clean your data...I've made an assumption that any time difference of less than a quarter of the overall shift time is anomalous data. This query will produce a "cleaned" table of information:

-- Reasonableness check on times on the table
DECLARE @CheckTime int = (SELECT DATEDIFF(hh, CAST(ToTime AS DateTime), CAST(FromTime AS DateTime)) FROM [shift] ) / 4

IF OBJECT_ID('tempdb..#cleaned') IS NOT NULL DROP TABLE #cleaned
;WITH clean AS
(
	SELECT EnrollNumber, DateInOut AS DateIn, 
		LAG(DateInOut) OVER (PARTITION BY EnrollNumber ORDER BY EnrollNumber, DateInOut) DatePrev
	FROM tblCheckInOut
)	
SELECT DISTINCT EnrollNumber, 
CASE WHEN DATEDIFF(hh, DatePrev, DateIn) < @CheckTime THEN DatePrev ELSE DateIn END AS DateInOut
INTO #cleaned
FROM clean T1

表 #cleaned 现在包含

EnrollNumber DateInOut
15045        2016-04-11 22:00:00
15045        2016-04-12 06:00:00
15045        2016-04-12 22:00:00
15045        2016-04-13 06:00:00

以下查询将以您需要的格式显示数据：

The following query will then present the data in the format you require:

;with q AS
(
	SELECT EnrollNumber, DateInOut AS DateIn, 
        LEAD(DateInOut) OVER (PARTITION BY EnrollNumber ORDER BY EnrollNumber, DateInOut) DateOut,
	ROW_NUMBER() OVER (PARTITION BY EnrollNumber ORDER BY EnrollNumber, DateInOut) rn
	FROM #cleaned t
)
SELECT * FROM q 
WHERE DateOut IS NOT NULL AND rn%2<>0
ORDER BY EnrollNumber, DateIn

请注意，它适用于多个 EnrollNumber 源数据中的值，但是它确实假设第一个条目是一个签到班次。



如果你没有处理LAG / LEAD的SQL Server版本（发布SQL Server 2012，而不是Express版本或2014 Express）然后您必须按如下方式使用行号和CTE：

清理数据

Note that it will work for multiple EnrollNumber values in the source data, but it does assume that the first entry is a check-in-to-shift time.

If you do not have a version of SQL Server that handles LAG/LEAD (post SQL Server 2012, not the Express version, or 2014 Express) then you have to use row numbers and CTEs as follows:
To clean the data

DECLARE @CheckTime int = (SELECT DATEDIFF(hh, CAST(ToTime AS DateTime), CAST(FromTime AS DateTime)) FROM [shift] ) / 4

IF OBJECT_ID('tempdb..#cleaned') IS NOT NULL DROP TABLE #cleaned
;WITH clean AS
(
	SELECT EnrollNumber, DateInOut AS DateIn, 
		ROW_NUMBER() OVER (PARTITION BY EnrollNumber ORDER BY EnrollNumber, DateInOut) rn
	FROM tblCheckInOut
), clean2 AS
(
	SELECT DISTINCT clean.EnrollNumber, clean.DateIn, prev.DateIn as DatePrev, clean.rn
	FROM clean
	LEFT OUTER JOIN clean prev ON prev.rn = clean.rn - 1 
)
SELECT DISTINCT EnrollNumber, CASE WHEN DATEDIFF(hh, DatePrev, DateIn) < @CheckTime THEN DatePrev ELSE DateIn END AS DateInOut
INTO #cleaned
FROM clean2 T1

要获得结果：

To get the results:

;WITH q AS
(
	SELECT EnrollNumber, DateInOut AS DateIn
	,ROW_NUMBER() 
		OVER(PARTITION BY EnrollNumber  ORDER BY EnrollNumber, DateInOut) AS rn
	FROM #cleaned 
)
, q2 AS
(
	SELECT DISTINCT q.EnrollNumber, q.DateIn, nxt.DateIn as DateOut, q.rn
	FROM q
	LEFT OUTER JOIN q nxt ON nxt.rn = q.rn + 1 
)
SELECT * 
FROM q2
WHERE DateOut IS NOT NULL AND rn%2<>0

[再次编辑......超过1班]

============================================== =================

对上面的一些修正来处理第二组移位数据。

@CheckTime 计算需要稍微改变

===============================================================
Some corrections to the above to handle the second set of shift data.
The @CheckTime "calculation" needs to change slightly

DECLARE @CheckTime int = (SELECT TOP 1 DATEDIFF(hh, CAST(ToTime AS DateTime), CAST(FromTime AS DateTime)) FROM [shift] ) / 4

或者这个值可以是硬编码的。 />


真正的问题是试图整理原始数据

Alternatively this value could be hard-coded.

The real problem is trying to tidy up the raw data

EnrollNumber DateInOut
 15045 2016-04-11 22:00:00   - Clocks IN night shift
 15045 2016-04-11 23:00:00   - Clocks OUT? Anomalous entry
 15045 2016-04-12 06:00:00   - Clocks OUT night shift
 15045 2016-04-12 22:00:00   - Clocks IN night shift
 15045 2016-04-13 06:00:00   - Clocks OUT night shift
 15045 2016-04-14 06:00:00   - Clocks IN day shift
 15045 2016-04-14 22:00:00   - Clocks OUT day shift
 15045 2016-04-15 06:00:00   - Clocks IN day shift
 15045 2016-04-18 09:10:00   - Clocks OUT? Anomalous entry
 15045 2016-04-18 18:00:00   - CLOCKS OUT? Anomalous entry
 15045 2016-04-18 20:00:00   - CLOCKS OUT

我们没有关于什么班次的标识符员工打开了，我们不能指望他们总是按时按时输入或输出，所以在这种清理数据的方法（非常类似于上面的那个）我假设任何一个条目换班是正确的 - 即2016年4月18日，他们在轮班结束前退出。

We have no identifier as to what shift the employee is on, and we can't expect them to always clock in or out "on time" exactly, so in this method for cleaning the data (very similar to the one above) I've assumed that the first entry for any shift is the correct one - i.e. on 18-Apr-2016 they clocked out before the end of their shift.

IF OBJECT_ID('tempdb..#cleaned') IS NOT NULL DROP TABLE #cleaned
;WITH clean AS
(
	SELECT EnrollNumber, DateInOut AS DateIn, 
		ROW_NUMBER() OVER (PARTITION BY EnrollNumber ORDER BY EnrollNumber, DateInOut) rn
	FROM tblCheckInOut
)
select c1.EnrollNumber, c1.DateIn, c1.rn, c2.DateIn as prevDate, DATEDIFF(hh,C2.DateIn,C1.DateIn) AS diff
INTO #cleaned
from clean c1
LEFT OUTER JOIN clean c2 ON c2.rn = c1.rn - 1 AND c1.EnrollNumber=c2.EnrollNumber
WHERE ISNULL(DATEDIFF(hh, C2.DateIn, C1.DateIn),24) > @CheckTime

然后，获取数据的查询比我早期的版本更整洁......

Then the query to get the data out is a little neater than my earlier version...

;WITH q AS
(
	SELECT EnrollNumber, DateIn AS DateIn
	,ROW_NUMBER() 
		OVER(PARTITION BY EnrollNumber  ORDER BY EnrollNumber, DateIn) AS rn
	FROM #cleaned 
)
SELECT DISTINCT q.EnrollNumber, q.DateIn, nxt.DateIn as DateOut
FROM q
LEFT OUTER JOIN q nxt ON nxt.rn = q.rn + 1 
WHERE nxt.DateIn IS NOT NULL AND q.rn % 2 = 1
ORDER BY EnrollNumber, DateIn

如果你能找到一些没有异常条目的方法原始数据然后只是在最终查询中用原始表替换#cleaned - 这是（缺乏）质量数据使其变得更复杂

If you can find some way of not having anomalous entries in the raw data then just substitute your original table for #cleaned in the final query - it is the (lack of) quality data that is making it more complicated

这篇关于从生物识别中获得夜班出勤率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！