如何将不同日期的开始时间和结束时间拆分为一个每日表?

Question

我正在努力解决一个显示机器状态开始时间和结束时间的表格，我想对小时求和，但只考虑一天。

举个例子：

ID STATUS    START_TIME          END_TIME
01 OPERATIVE 12/01/2020 08:00:00 14/01/2020 08:00:00
02 OPERATIVE 11/01/2020 05:00:00 13/01/2020 02:00:00

我的预期结果会给我一个表格，显示11/01/2020,2号机器从05:00:00工作到23:59:59，所以它会19h。二号机器根本不起作用。

在12/01/2020年，一号机器从08:00:00工作到23:59:59-16h和第二台机器工作了一整天：24h，所以它会给我 24h + 16h 在这一天 =40h。

就这样，把我作为最后一张桌子的一部分：

DAY        WORKING_HOURS
11/01/2020 19H
12/01/2020 40H

那么，你们会推荐我做什么？我该如何解决？

提前感谢，

Answer 1

;WITH calcs AS (
-- 24 hours in seconds, minus 1 second for 23:59:59
SELECT CAST(24 * 60 * 60 - 1 AS INT) AS [t_const_1]
,   CAST(2 * 24 * 60 * 60 - 1 AS INT) AS [t_const_2]
)
,   timeCTE AS (
SELECT T.ID
,   T.START_TIME AS [start_dt]
-- Find the "start" of the day (00:00:00) and add our constant
,   DATEADD(SECOND, calcs.[t_const_1], CAST(CAST(T.START_TIME AS DATE) AS DATETIME)) AS [end_dt_1]
,   T.END_TIME AS [end_dt]
FROM #_tmp AS T, calcs
UNION ALL
SELECT T1.ID
,   DATEADD(HOUR, 24, CAST(CAST(T1.[end_dt_1] AS DATE) AS DATETIME)) AS [start_dt]
-- Find the "start" of the day (00:00:00) and add our constant times 2
,   DATEADD(SECOND, calcs.[t_const_2], CAST(CAST(T1.[end_dt_1] AS DATE) AS DATETIME)) AS [end_dt_1]
,   T1.[end_dt]
FROM timeCTE AS T1, calcs
WHERE CAST(T1.[end_dt_1] AS DATE) < CAST(T1.[end_dt] AS DATE)
)
,   finalCTE AS (
SELECT T2.ID
,   CONVERT(DATETIME, T2.[start_dt]) AS [start_dt]
,   CASE
WHEN T2.[end_dt_1] > T2.[end_dt] THEN T2.[end_dt]
ELSE T2.[end_dt_1]
END AS [end_date]
FROM timeCTE AS T2
)
SELECT fin.[ID]
,   CONVERT(DATETIME, fin.start_dt) AS [start_date]
,   CONVERT(DATETIME, fin.end_date) AS [end_date]
,   DATEPART(HOUR, fin.end_date) - DATEPART(HOUR, fin.[start_dt]) AS [net_daily_hours]
FROM finalCTE AS fin

输出：

ID  start_dt                end_date                net_hours
01  2020-01-12 08:00:00.000 2020-01-12 23:59:59.000 15
02  2020-01-11 05:00:00.000 2020-01-11 23:59:59.000 18
02  2020-01-12 00:00:00.000 2020-01-12 23:59:59.000 23
02  2020-01-13 00:00:00.000 2020-01-13 02:00:00.000 2
01  2020-01-13 00:00:00.000 2020-01-13 23:59:59.000 23
01  2020-01-14 00:00:00.000 2020-01-14 08:00:00.000 8

主要来自这篇文章。