sql - 按类次分组时间戳

Question

在我们工厂，我们有以下类次:

• 1:周一至周五:上午 5 点至下午 1 点
• 2:周一至周五:下午 1 点至晚上 9 点
• 3:周一至周五:晚上 9 点至凌晨 5 点
• 4a:星期六:早上 5 点到下午 5 点
• 4b:周日:下午 5 点 - 周一凌晨 5 点

在某个表中，我们有几个日期时间条目，我想使用查询对其进行分组。对于类次 1、2 和 3，此查询工作正常::

  CASE
WHEN DATEPART(hh, c.date_time_stamp) >= 5 AND DATEPART(hh, c.date_time_stamp) < 13 THEN CONVERT(VARCHAR(10), c.date_time_stamp, 103) + ' shift 1'
WHEN DATEPART(hh, c.date_time_stamp) >= 13 AND DATEPART(hh, c.date_time_stamp) < 21 THEN CONVERT(VARCHAR(10), c.date_time_stamp, 103) + ' shift 2'
WHEN DATEPART(hh, c.date_time_stamp) >= 21 AND DATEPART(hh, c.date_time_stamp) < 24 THEN CONVERT(VARCHAR(10), c.date_time_stamp, 103) + ' shift 3'
WHEN DATEPART(hh, c.date_time_stamp) >= 00 AND DATEPART(hh, c.date_time_stamp) < 5 THEN CONVERT(VARCHAR(10), DATEADD(DAY, -1, c.date_time_stamp), 103)  + ' shift 3'  END AS shift,

这给了我某种 shift_id。为了识别类次 4a 和 4b，我可以扩展 case 语句，但我有点担心查询的性能。

谁能告诉我如何创建一个以高效方式返回 shift_id 的函数？谢谢!

编辑:一些示例数据:

inventory_trans_number creation_dt             shift_id             shift_type container_code
---------------------- ----------------------- -------------------- ---------- ------------------
140952                 2013-02-04 01:03:19.043 20130203 03          3          154143591115247892
140956                 2013-02-04 01:07:20.343 20130203 03          3          154143591115247939
140962                 2013-02-04 01:10:56.417 20130203 03          3          154143591115247991
140968                 2013-02-04 01:14:55.250 20130203 03          3          154143591115248134
140970                 2013-02-04 01:17:18.883 20130203 03          3          154143591115248196
141070                 2013-02-04 02:12:59.327 20130203 03          3          154143591115248240
141076                 2013-02-04 02:16:27.480 20130203 03          3          154143591115248356
141092                 2013-02-04 02:22:44.067 20130203 03          3          154143591115248530
141096                 2013-02-04 02:25:02.157 20130203 03          3          154143591115248585
141102                 2013-02-04 02:33:51.253 20130203 03          3          154143591115248615

Answer 1

这将为您提供转变。它的工作原理是意识到这是一个数学问题，而不是数据库问题。

每周有 21 个类次，我们创建了一个表变量来保存类次名称 - 您可能想为此创建一个永久表，并在 Number 列上创建一个索引。

通过在我们感兴趣的任何日期之前设置为星期一 5:00 的时间和日期时间之间的小时获取 DATEDIFF，并取将其与 24*7(一周中的小时数)取模，我们得到一个数字，指示周一 5:00 后的小时数(即类次 0)。将其除以 8 即可得到类次编号。

DECLARE @FirstEverShift datetime = CONVERT(datetime, '2011-12-26 05:00')

DECLARE @ShiftTypes TABLE
(Number int NOT NULL PRIMARY KEY,
 Shift  nvarchar(2) NOT NULL)

INSERT @ShiftTypes
VALUES
(0, '1'),
(1, '2'),
(2, '3'),
(3, '1'),
(4, '2'),
(5, '3'),
(6, '1'),
(7, '2'),
(8, '3'),
(9, '1'),
(10, '2'),
(11, '3'),
(12, '1'),
(13, '2'),
(14, '3'),
(15, '4a'),
(16, 'NA'),
(17, 'NA'),
(18, '4b'),
(19, 'NA'),
(20, 'NA')

SELECT  s.date_time_stamp
        ,st.Shift
FROM    shifts s
        INNER JOIN
        @ShiftTypes st ON st.Number=DATEDIFF(HOUR, @FirstEverShift, s.date_time_stamp) % (24*7) / 8

编辑

我意识到周末轮类是 12 小时而不是 8 小时。这种方法有效但需要修改，我不会这样做，因为我在平板电脑上!

不是除以 8，而是除以 4，这意味着表变量必须从 0 到 40，每 8 小时轮类需要 2 个数字，每 12 小时需要 3 个数字。