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

我要创建一个棘手的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有什么简单的方法吗？

最佳答案

正如@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

关于mysql - 逆DateDiff的SQL SERVER(TSQL)SUM(SQL或存储过程)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/29091671/