组顺序SQL记录 [英] Group Sequential SQL Records

问题描述

寻找一种将连续的时间记录分组为一行的方法.

Looking for a way to group sequential timeclock records into a single row.

源系统具有一个身份列，员工ID，日期和进/出标志(1 =进&2 =出).请注意

The source system has an identity column, employee id, date and in/out flag (1=in & 2=out). Note that the

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

I am looking for sql that would give me the following result

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语句提取的一致的东西).下面没有间断示例:

Note that the ID in the source system is not always sequential because of timeclock edits. Date should be chronological. If only two punches exist, I will need to have the clock in and clock out dates populated with no breaks (or something consistent that I can extract with a case statement). No breaks example below:

EmployeeID ClockIn             BreakStart          BreakEnd            ClockOut
5890       2008-08-19 14:07:14                                         2008-08-19 22:06:18

Sql版本是2008 R2

Sql version is 2008 R2

在此先感谢您，我不知道如何使它始终如一地工作，非常感谢您的帮助.

Thanks in advance, I can't figure out how to get this to work consistently and your help is greatly appreciated.

推荐答案

您可以使用 ROW_NUMBER()函数和带窗口的 COUNT()函数来处理此问题没有休息日:

You can do this with the ROW_NUMBER() function and a windowed COUNT() to handle the no break days:

;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提琴

这是按天设置的，因此午夜之后的超时将不起作用，并且打孔次数奇数也有问题，但是对于像您的示例这样的简单世界来说，这样做就可以了.

This is set by day, so a clockout after midnight wouldn't work, and odd number of punches would also be problematic, but for a simple world like your example this will do.

这篇关于组顺序SQL记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！