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

pw136qt2  于 12个月前  发布在  其他
关注(0)|答案(1)|浏览(70)
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.

sql-server

1条答案

按热度按时间
k5hmc34c

k5hmc34c1#

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);
ShiftIDShiftStartDateTimeUTCShiftEndDateTimeUTCEventIDActualEventStartDateTimeUTCActualEventEndDateTimeUTCModifiedEventStartDateTimeUTCModifiedEventEndDateTimeUTC
12023-09-20 00:00:00.0002023-09-20 07:59:59.99742023-09-20 02:45:00.0002023-09-20 02:45:35.0002023-09-20 02:45:00.0002023-09-20 02:45:35.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.99762023-09-20 03:06:00.0002023-09-20 03:06:05.0002023-09-20 03:06:00.0002023-09-20 03:06:05.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997112023-09-20 04:57:00.0002023-09-20 04:57:04.0002023-09-20 04:57:00.0002023-09-20 04:57:04.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997122023-09-20 01:02:00.0002023-09-20 01:02:37.0002023-09-20 01:02:00.0002023-09-20 01:02:37.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997132023-09-20 06:13:00.0002023-09-20 06:13:40.0002023-09-20 06:13:00.0002023-09-20 06:13:40.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997182023-09-20 00:46:00.0002023-09-20 00:46:37.0002023-09-20 00:46:00.0002023-09-20 00:46:37.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997192023-09-20 01:53:00.0002023-09-20 01:53:16.0002023-09-20 01:53:00.0002023-09-20 01:53:16.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997202023-09-20 06:39:00.0002023-09-20 06:39:16.0002023-09-20 06:39:00.0002023-09-20 06:39:16.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997212023-09-20 00:42:00.0002023-09-20 00:42:13.0002023-09-20 00:42:00.0002023-09-20 00:42:13.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997232023-09-20 04:43:00.0002023-09-20 04:43:29.0002023-09-20 04:43:00.0002023-09-20 04:43:29.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997272023-09-20 01:08:00.0002023-09-20 01:08:56.0002023-09-20 01:08:00.0002023-09-20 01:08:56.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997302023-09-20 01:58:00.0002023-09-20 01:58:55.0002023-09-20 01:58:00.0002023-09-20 01:58:55.000
12023-09-20 00:00:00.0002023-09-20 07:59:59.997342023-09-20 03:27:00.0002023-09-20 03:27:13.0002023-09-20 03:27:00.0002023-09-20 03:27:13.000
........................
32023-09-20 16:00:00.0002023-09-20 23:59:59.9971082023-09-20 21:06:00.0002023-09-20 21:06:17.0002023-09-20 21:06:00.0002023-09-20 21:06:17.000
32023-09-20 16:00:00.0002023-09-20 23:59:59.9971102023-09-20 16:34:00.0002023-09-20 16:34:38.0002023-09-20 16:34:00.0002023-09-20 16:34:38.000
32023-09-20 16:00:00.0002023-09-20 23:59:59.9971112023-09-20 21:32:00.0002023-09-20 21:32:44.0002023-09-20 21:32:00.0002023-09-20 21:32:44.000
32023-09-20 16:00:00.0002023-09-20 23:59:59.9971142023-09-20 20:48:00.0002023-09-20 20:48:52.0002023-09-20 20:48:00.0002023-09-20 20:48:52.000
赞(0）分享  回复(0）举报  12个月前
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com