How to split start time and end time from different days into a daily table?

Question

I am struggling with a table that show me a start time and an end time of a machine status, and I would like to sum the hours but only considering day by day.

As an example:

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

My expected result would give me a table showing that in 11/01/2020, the machine number 2 worked from 05:00:00 to 23:59:59, so it would be 19h. Machine number two did not work at all.

In 12/01/2020, machine number one worked from 08:00:00 til 23:59:59 - 16h and machine number two worked the whole day: 24h, so it would give me 24h + 16h in this day = 40h.

And so it goes, giving me as part of the final table:

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

So, what would you guys recommend me to do? How can I solve it?

Thanks in advance,

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

Output:

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

Mostly drawn from this post.