mysql - SQL SERVER (TSQL) 逆 DateDiff 的 SUM(SQL 或存储过程)

Question

我有一个棘手的 SQL 需要创建，有这个表:

+-----------------------+
| Employee Login Logout |
+-----------------------+
| 1        08:30 08:50  |
| 1        09:00 10:00  |
| 1        10:20 11:00  |
+-----------------------+

我需要一个 sql 来总结所有的休息时间:

08:50 -> 09:00 = 10 minutes +
10:00 -> 10:20 = 20 minutes =
30 minutes in total

所以我需要以某种方式检查每条记录并使用 datediff 方法 sum 记录之间的中断持续时间。

有什么简单的方法可以使用 SQL 或 Stored Procedure 来实现吗？

Answer 1

正如@thebjorn 所评论的那样，您可以首先获得第一次登录和最后一次注销(轮类)之间的差异，然后减去每个登录-注销对的差异的SUM(实际时间正在工作)。结果将是您不工作的总时间或您的休息时间。

;WITH SampleData(Employee, Login, Logout) AS(
    SELECT 1, CAST('08:30' AS TIME), CAST('08:50' AS TIME) UNION ALL
    SELECT 1, CAST('09:00' AS TIME), CAST('10:00' AS TIME) UNION ALL
    SELECT 1, CAST('10:20' AS TIME), CAST('11:00' AS TIME)
)
SELECT 
    Employee,
    TotalBreakTime = DATEDIFF(MINUTE, MIN(Login), MAX(Logout)) -  SUM(DATEDIFF(MINUTE, Login, Logout))
FROM SampleData
GROUP BY Employee

---

使用LAG函数

;WITH SampleData(Employee, Login, Logout) AS(
    SELECT 1, CAST('08:30' AS TIME), CAST('08:50' AS TIME) UNION ALL
    SELECT 1, CAST('09:00' AS TIME), CAST('10:00' AS TIME) UNION ALL
    SELECT 1, CAST('10:20' AS TIME), CAST('11:00' AS TIME) UNION ALL
    SELECT 2, CAST('10:00' AS TIME), CAST('10:30' AS TIME) UNION ALL
    SELECT 2, CAST('10:45' AS TIME), CAST('11:30' AS TIME)
)
,CteBreakTime AS(
    SELECT *,
        BreakTime = DATEDIFF(MINUTE, LAG(Logout) OVER(PARTITION BY Employee ORDER BY Login), Login)
    FROM SampleData
)
SELECT
    Employee,
    TotalBreakTime = SUM(BreakTime) 
FROM CteBreakTime
GROUP BY Employee