寻找一种将连续的时间记录分组为一行的方法。
源系统具有一个身份列,员工ID,日期和进/出标记(1 =输入和2 =输出)。请注意
ID EmployeeID DATE InOut
1019374 5890 2008-08-19 14:07:14 1
1019495 5890 2008-08-19 18:17:08 2
1019504 5890 2008-08-19 18:50:40 1
1019601 5890 2008-08-19 22:06:18 2
我正在寻找会给我以下结果的sql
EmployeeID ClockIn BreakStart BreakEnd ClockOut
5890 2008-08-19 14:07:14 2008-08-19 18:17:08 2008-08-19 18:50:40 2008-08-19 22:06:18
请注意,由于对时钟的编辑,源系统中的ID并不总是连续的。日期应按时间顺序。如果仅存在两次打孔,则需要不间断地填充时钟输入和时钟输出日期(或者可以使用case语句提取的一致的东西)。下面没有间断示例:
EmployeeID ClockIn BreakStart BreakEnd ClockOut
5890 2008-08-19 14:07:14 2008-08-19 22:06:18
SQL版本是2008 R2
在此先感谢您,我不知道如何使它始终如一地工作,非常感谢您的帮助。
您可以使用ROW_NUMBER()
函数和带窗口的窗口COUNT()
来执行此操作,以处理不休息日:
;with cte as (SELECT *,ROW_NUMBER() OVER(PARTITION BY EmployeeID, CAST(dt AS DATE) ORDER BY dt) RN
,COUNT(*) OVER(PARTITION BY EmployeeID, CAST(dt AS DATE)) Dt_CT
FROM Table1)
SELECT EmployeeID
,Dt = CAST(dt AS DATE)
,ClockIn = MAX(CASE WHEN RN = 1 THEN DT END)
,BreakStart = MAX(CASE WHEN Dt_CT = 4 AND RN = 2 THEN DT END)
,BreakEnd = MAX(CASE WHEN Dt_CT = 4 AND RN = 3 THEN DT END)
,ClockOut = MAX(CASE WHEN (Dt_CT = 2 AND RN = 2) OR RN = 4 THEN DT END)
FROM cte
GROUP BY EmployeeID
,CAST(dt AS DATE)
演示:SQL Fiddle
这是按天设置的,因此午夜后的超时将不起作用,并且打孔次数奇数也有问题,但是对于像您的示例这样的简单世界来说,这样做就可以了。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句