SELECT rows from my table where the start and end times are within my shift ranges

Question

SELECT 
    [Machine_Events_ID],
    [Event_TimeStamp],
    [Event_EndTimeStamp],
    [Bucket_ID],
    [PartNumber]
FROM 
    [A5].[dbo].[Machine_Events]
WHERE 
    DATEPART(hour, Event_TimeStamp) > shift1DateTime 
    AND DATEPART(hour, Event_EndTimeStamp) < shift2DateTime 

There is a lot to explain here, since the code is far from what I need.

I need to pass in a shift set like (1,2,3) or (1,3) like this where depending on which shifts or in that set I would only return rows from those shifts going off of the two timestamps. In addition to that, if an event transitions to the previous or next shift this query would return that time as the actual end of the shift like this idea:

CASE 
    WHEN a.Event_TimeStamp <= '2023-09-15 10:00:00.000' 
        THEN '2023-09-15 10:00:00.000' 
    ELSE a.Event_TimeStamp 
END

CASE 
    WHEN a.Event_EndTimeStamp >= '2023-09-20 10:00:00.000' 
        THEN '2023-09-20 10:00:00.000' 
    ELSE a.Event_EndTimeStamp 
END

I am going to pass in the start and end times for the overall time range I am looking to limit on in addition to passing in the shift ranges,

In my example I am passing in '2023-09-15 10:00:00.000' as the start time and '2023-09-20 10:00:00.000' as the end time.

Answer 1

When asking questions like this it's really helpful to provide example DDL/DML to demonstrate what you're working with. In this case it's pretty trivial to just have the engine spit out some randomize data within a range:

DECLARE @Shifts TABLE (ShiftID INT IDENTITY, ShiftStartDateTimeUTC DATETIME, ShiftEndDateTimeUTC DATETIME);
INSERT INTO @Shifts (ShiftStartDateTimeUTC, ShiftEndDateTimeUTC) VALUES 
('2023-09-20 00:00:00', '2023-09-20 07:59:59.997'), ('2023-09-20 08:00:00', '2023-09-20 15:59:59.997'), ('2023-09-20 16:00:00', '2023-09-20 23:59:59.997');

DECLARE @MachineEvents TABLE (EventID INT IDENTITY, EventStartDateTimeUTC DATETIME, EventEndDateTimeUTC DATETIME);
INSERT INTO @MachineEvents (EventStartDateTimeUTC, EventEndDateTimeUTC)
SELECT DATEADD(MINUTE,ROUND(((59 - 0) * Rnd2 + 0), 0),DATEADD(HOUR,ROUND(((23 - 0) * Rnd1 + 0), 0),CAST(CAST(GETDATE() AS DATE) AS DATETIME))) AS EventStartDateTimeUTC,
       DATEADD(SECOND,ROUND(((59 - 0) * Rnd3 + 0), 0),DATEADD(MINUTE,ROUND(((59 - 0) * Rnd2 + 0), 0),DATEADD(HOUR,ROUND(((23 - 0) * Rnd1 + 0), 0),CAST(CAST(GETDATE() AS DATE) AS DATETIME)))) AS EventEndDateTImeUTC
  FROM (VALUES ((RAND(CAST(NEWID() AS VARBINARY))), (RAND(CAST(NEWID() AS VARBINARY))), (RAND(CAST(NEWID() AS VARBINARY))))) a(Rnd1,Rnd2,Rnd3)
  CROSS APPLY sys.sysobjects
UNION ALL
SELECT '2023-09-20 07:59:59.997', '2023-09-20 08:03:59.997';
/* Added an overlapping one */

This gives us a set of three shifts, each 8 hours long over today and some data with events occurring somewhat randomly throughout the day.

With that done, it's fairly straight forward to collect the data by shift and manipulate the results when there's a shift foundry cross:

SELECT s.ShiftID, s.ShiftStartDateTimeUTC, s.ShiftEndDateTimeUTC, me.EventID, 
       me.EventStartDateTimeUTC AS ActualEventStartDateTimeUTC, me.EventEndDateTimeUTC AS ActualEventEndDateTimeUTC,
       CASE WHEN me.EventStartDateTimeUTC < s.ShiftStartDateTimeUTC THEN s.ShiftStartDateTimeUTC ELSE me.EventStartDateTimeUTC END AS ModifiedEventStartDateTimeUTC,
       CASE WHEN me.EventEndDateTimeUTC > s.ShiftEndDateTimeUTC THEN s.ShiftEndDateTimeUTC ELSE me.EventEndDateTimeUTC END AS ModifiedEventEndDateTimeUTC
  FROM @Shifts s
    INNER JOIN @MachineEvents me
      ON me.EventStartDateTimeUTC BETWEEN s.ShiftStartDateTimeUTC AND s.ShiftEndDateTimeUTC
      OR me.EventEndDateTimeUTC BETWEEN s.ShiftStartDateTimeUTC AND s.ShiftEndDateTimeUTC
 WHERE s.ShiftID IN (1,3);

ShiftID	ShiftStartDateTimeUTC	ShiftEndDateTimeUTC	EventID	ActualEventStartDateTimeUTC	ActualEventEndDateTimeUTC	ModifiedEventStartDateTimeUTC	ModifiedEventEndDateTimeUTC
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	4	2023-09-20 02:45:00.000	2023-09-20 02:45:35.000	2023-09-20 02:45:00.000	2023-09-20 02:45:35.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	6	2023-09-20 03:06:00.000	2023-09-20 03:06:05.000	2023-09-20 03:06:00.000	2023-09-20 03:06:05.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	11	2023-09-20 04:57:00.000	2023-09-20 04:57:04.000	2023-09-20 04:57:00.000	2023-09-20 04:57:04.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	12	2023-09-20 01:02:00.000	2023-09-20 01:02:37.000	2023-09-20 01:02:00.000	2023-09-20 01:02:37.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	13	2023-09-20 06:13:00.000	2023-09-20 06:13:40.000	2023-09-20 06:13:00.000	2023-09-20 06:13:40.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	18	2023-09-20 00:46:00.000	2023-09-20 00:46:37.000	2023-09-20 00:46:00.000	2023-09-20 00:46:37.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	19	2023-09-20 01:53:00.000	2023-09-20 01:53:16.000	2023-09-20 01:53:00.000	2023-09-20 01:53:16.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	20	2023-09-20 06:39:00.000	2023-09-20 06:39:16.000	2023-09-20 06:39:00.000	2023-09-20 06:39:16.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	21	2023-09-20 00:42:00.000	2023-09-20 00:42:13.000	2023-09-20 00:42:00.000	2023-09-20 00:42:13.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	23	2023-09-20 04:43:00.000	2023-09-20 04:43:29.000	2023-09-20 04:43:00.000	2023-09-20 04:43:29.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	27	2023-09-20 01:08:00.000	2023-09-20 01:08:56.000	2023-09-20 01:08:00.000	2023-09-20 01:08:56.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	30	2023-09-20 01:58:00.000	2023-09-20 01:58:55.000	2023-09-20 01:58:00.000	2023-09-20 01:58:55.000
1	2023-09-20 00:00:00.000	2023-09-20 07:59:59.997	34	2023-09-20 03:27:00.000	2023-09-20 03:27:13.000	2023-09-20 03:27:00.000	2023-09-20 03:27:13.000
...	...	...	...	...	...	...	...
3	2023-09-20 16:00:00.000	2023-09-20 23:59:59.997	108	2023-09-20 21:06:00.000	2023-09-20 21:06:17.000	2023-09-20 21:06:00.000	2023-09-20 21:06:17.000
3	2023-09-20 16:00:00.000	2023-09-20 23:59:59.997	110	2023-09-20 16:34:00.000	2023-09-20 16:34:38.000	2023-09-20 16:34:00.000	2023-09-20 16:34:38.000
3	2023-09-20 16:00:00.000	2023-09-20 23:59:59.997	111	2023-09-20 21:32:00.000	2023-09-20 21:32:44.000	2023-09-20 21:32:00.000	2023-09-20 21:32:44.000
3	2023-09-20 16:00:00.000	2023-09-20 23:59:59.997	114	2023-09-20 20:48:00.000	2023-09-20 20:48:52.000	2023-09-20 20:48:00.000	2023-09-20 20:48:52.000