query that can count and check process time if it is in shift schedule range

Question

I have 2 tables
1st table has startprocesstime and endprocesstime
2nd table has EmployeeID, StartShift and EndShift.

I need to check if the startprocesstime and Endprocesstime are still in range of my shiftschedule.

Example: Scenario 1 : Startprocesstime 2023-11-18 4:30:000 Endprocessime 2023-11-18 4:31:000
Scenario 2: Startprocesstime 2023-11-19 00:30:000 Endprocessime 2023-11-19 00:31:000

EmployeedID  4pm(StartShift) to 1am(EndShift).

Answer 1

One approach is to cross join the processes with the shifts and then replicate the shifts across all dates covered by each process. Note that the first candidate shift might start on the day before the process start, if that shift crosses midnight.

The process and shift date/time ranges can then be checked for overlaps using the standard test start1 < end2 AND start2 < end1. For each overlap, the amount of overlap (in minutes) can be calculated as DATEDIFF(minute, GREATEST(start1, start2), LEAST(end1, end2)).

Finally, since there are potentially multiple results with the same process/shift combination across multiple days, GROUP BY can be used to combine them and SUM() applied to total up the calculated overlap.

The resulting query would be something like:

SELECT
    P.Name AS PName
    ,CONVERT(SMALLDATETIME, P.Start) AS PStart
    ,CONVERT(SMALLDATETIME, P.Finish) AS PFinish
    ,SUM(DATEDIFF(minute, GREATEST(P.Start, SS.Start), LEAST(P.Finish, SS.Finish))) AS Minutes
    ,S.Name AS SName
    ,CONVERT(CHAR(5), S.Start, 8) AS SStart
    ,CONVERT(CHAR(5), S.Finish, 8) AS SFinish
FROM (
    -- Process data plus some calculated fields
    SELECT
        P.*,
        CONVERT(DATE, P.Start) AS StartDate,
        DATEDIFF(day, P.Start, P.Finish) AS Days
    FROM Process P
) P
CROSS JOIN (
    -- Shift data plus a check for midnight crossover
    SELECT
        S.*,
        CASE WHEN S.Start > S.Finish THEN 1 ELSE 0 END AS CrossOver
    FROM Shift S
) S
CROSS APPLY (
    -- Replicate shifts across all dates covered by each process
    SELECT
        CONVERT(DATETIME, DATEADD(day, G.value, P.StartDate)) + CONVERT(DATETIME, S.Start) AS Start,
        CONVERT(DATETIME, DATEADD(day, G.value + S.Crossover, P.StartDate)) + CONVERT(DATETIME, S.Finish) AS Finish  
    FROM GENERATE_SERIES(-S.Crossover, P.Days) G
) SS
WHERE (P.Start < SS.Finish AND SS.Start < P.Finish) -- overlap test
GROUP BY
    P.Name, P.Start, P.Finish
    ,S.Name, S.Start, S.Finish
ORDER BY P.Name, S.Name

Sample results (with some additional test data):

PName	PStart	PFinish	Minutes	SName	SStart	SFinish
Process 1	2023-11-18 04:30	2023-11-18 04:31	1	Shift 2	23:00	07:00
Process 2	2023-11-19 00:30	2023-11-19 00:31	1	Shift 1	16:00	01:00
Process 2	2023-11-19 00:30	2023-11-19 00:31	1	Shift 2	23:00	07:00
Process 3	2023-11-22 23:45	2023-11-23 00:15	30	Shift 1	16:00	01:00
Process 3	2023-11-22 23:45	2023-11-23 00:15	30	Shift 2	23:00	07:00
Process 4	2023-11-24 23:45	2023-11-27 00:15	1110	Shift 1	16:00	01:00
Process 4	2023-11-24 23:45	2023-11-27 00:15	990	Shift 2	23:00	07:00
Process 4	2023-11-24 23:45	2023-11-27 00:15	120	Shift 3	01:00	02:00
Process 4	2023-11-24 23:45	2023-11-27 00:15	1080	Shift 4	07:00	16:00
Process 4	2023-11-24 23:45	2023-11-27 00:15	1080	Shift 5	12:00	21:00

Note that is the source data is limited to just Shift 1 and Process 1 and Process 2 from the original OP posted data, there is only one match with a 1 minute overlap.

See this db<>fiddle.

I had also attempted a different approach where both the shifts and the process date/time ranges were split up at all midnight crossovers and reduced to time-only ranges. Although the same results were achieved, the code was more complicated. I have not posted that source here, but have included it at the end of the above fiddle.

The above code uses the the LEAST(), GREATEST(), and GENERATE_SERIES() functions that are new in SQL Server 2022. If you are using an older version, you will need to substitute in replacements. LEAST() and GREATEST() can be replaced with CASE expressions. As for GENERATE_SERIES(), that gets more involved...

        -- GREATEST(P.Start, SS.Start) replacement
        CASE WHEN P.Start > SS.Start THEN P.Start ELSE SS.Start END

        -- LEAST(P.Finish, SS.Finish) replacement
        CASE WHEN P.Finish < SS.Finish THEN P.Finish ELSE SS.Finish

    -- GENERATE_SERIES(-S.Crossover, P.Days) replacement
    FROM (
        SELECT TOP (P.Days + S.Crossover + 1)
            -1 - S.Crossover + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Value
        FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) A(N)
        CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) B(N)
        CROSS JOIN (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) C(N)
    ) G

See this db<>fiddle for code that runs with older SQL Server versions.