SQL:同一天的和时间戳间隔

Question

我正在设置一个新的SQL查询，以汇总员工出勤表中的记录。这些记录从指纹或RFID传感器下载并记录在同一张表上。我想得到工作时间的多少。

如果雇员每天来一次并离开工作场所，一切都很好。设备在表上生成两个记录，这不是一个问题。很容易识别出入口时间。

但我不知道如何解决，如果一个人进来，休息(离开工作场所)，然后他再次进来，直到退出时间。

假设它们总是每个间隔上的记录(到达和退出时间戳)。另外，员工从不在一天内登记，并在第二天离开。

我有以下查询。记住:这只会得到最小时间戳(到达时间)和最大时间戳(离开时间)。

SELECT Userid, Name, Date, Entrance, Exit, Hours FROM
            (SELECT Userid AS user,
            CONVERT(VARCHAR, CONVERT(TIME, min(Checktime))) AS Entrance,
            CONVERT(VARCHAR, CONVERT(TIME, max(Checktime))) AS Exit,
            CONVERT(VARCHAR, CONVERT(TIME, max(Checktime)-min(CheckTime))) AS Hours,
            CONVERT(VARCHAR, CONVERT(DATE, CheckTime)) AS Fecha,
            COUNT(*) AS Regs,
            SUM(edited) AS edited FROM attendance
            WHERE CONVERT(DATE, CheckTime) < CONVERT(DATE, GETDATE())
            GROUP BY Userid, CONVERT(DATE, CheckTime)) AS Hs
            INNER JOIN Userinfo
            ON Userinfo.Userid = Hs.user
            ORDER BY Date DESC, Name ASC;

例如，如果表有以下记录：

id  |  Logid   |  Userid  |       CheckTime       |  edited
1   |      10  |       1  |    2019-06-18 8:00:00 |     0
2   |      11  |       1  |   2019-06-18 12:00:00 |     0
3   |      12  |       1  |   2019-06-18 15:00:00 |     0
4   |      13  |       1  |   2019-06-18 17:00:00 |     0
5   |      14  |       2  |    2019-06-18 8:00:00 |     0
6   |      15  |       2  |   2019-06-18 17:00:00 |     0

我得到的是：

Userid  |  Name     |     Date     |  Entrance  |   Exit   |  Hours  |  edited
     1  |  Gandalf  |  2019-06-18  |    8:00:00 | 17:00:00 | 9:00:00 |      0
     2  |    Frodo  |  2019-06-18  |    8:00:00 | 17:00:00 | 9:00:00 |      0

我需要的是：

Userid  |  Name     |     Date     |  Entrance  |   Exit   |  Hours  |  edited
     1  |  Gandalf  |  2019-06-18  |    8:00:00 | 17:00:00 | 6:00:00 |      0
     2  |    Frodo  |  2019-06-18  |    8:00:00 | 17:00:00 | 9:00:00 |      0

计算总时间为(12:00:00 - 8:00:00) + (17:00:00 - 15:00:00)。在这种情况下，“入口”和“出口”栏根本没有必要。

你知道我怎么能解决这个问题吗？非常感谢!

Answer 1

这假设您有对、enter/exit和处理多个中断。

SQL演示

with cte as (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY [Userid], cast ([CheckTime] as Date) 
                               ORDER BY [CheckTime]) as rn
  FROM Table1 t1
)  
SELECT c1.[Userid], 
       cast (c1.[CheckTime] as Date) as the_day,
       SUM (DATEDIFF (hh, c1.[CheckTime], c2.[CheckTime])) as total_hours
FROM cte c1
JOIN cte c2
  ON c1.rn = c2.rn -1
 AND c1.[Userid] = c2.[Userid]
 AND c1.rn % 2 = 1
GROUP BY c1.[Userid],
         cast (c1.[CheckTime] as Date) ;

输出

| Userid |    the_day | total_hours |
|--------|------------|-------------|
|      1 | 2019-06-18 |           6 |
|      2 | 2019-06-18 |           9 |

注：

DATEDIFF的通用语法

DATEDIFF(datepart, start_date, end_date)

只要实现DATEDIFF函数，就可以计算两个日期值之间的时间间隔，并将其作为整数返回。

因此，如果您有08:00和09:30使用hh作为日期部分，您仍然可以得到1h。也许最好用mi除以60

Answer 2

太棒了！胡安·卡洛斯的解决方案效果很好！

我之所以发布这篇文章，是因为我已经编辑了他的一些代码，以满足最初的职位要求。

代码完全相同。只是我改变了/增加了几行

with cte as (
  SELECT  *, ROW_NUMBER() OVER (PARTITION BY [Userid], cast ([CheckTime] as Date)
                               ORDER BY [CheckTime]) as rn
  FROM Table1 t1
  WHERE CAST(CheckTime AS DATE) = '2019-06-17'  -- Filter by specific date 
)
SELECT c1.[Userid],
       cast (c1.[CheckTime] as Date) as the_day,
       -- Return time as HH:MM
       CONVERT(VARCHAR, SUM (DATEDIFF (SECOND , c1.[CheckTime], c2.[CheckTime]))/3600) + ':' + right('00' + CONVERT(VARCHAR, CONVERT(FLOAT, (SUM (DATEDIFF (SECOND , c1.[CheckTime], c2.[CheckTime]))/60) - ((SUM (DATEDIFF (SECOND , c1.[CheckTime], c2.[CheckTime]))/3600)*60))),2) as total_time
FROM cte c1
JOIN cte c2
  ON c1.rn = c2.rn -1
 AND c1.[Userid] = c2.[Userid]
 AND c1.rn % 2 = 1
GROUP BY c1.[Userid],
         cast (c1.[CheckTime] as Date);

此查询返回：

| Userid |    the_day |  total_time |
|--------|------------|-------------|
|      1 | 2019-06-18 |        6:00 |
|      2 | 2019-06-18 |        9:00 |