Group stamps by change
In our factory, we have the following shifts:
- 1: Mon - Fri: 5:00 - 13:00
- 2: Mon - Fri: from 13:00 to 21:00
- 3: Mon - Fri: 9:00 am - 5:00 am
- 4a: Sat: 5:00 a.m. - 5:00 p.m.
- 4b: Sun: 5pm - Mon 5am
In a specific table, we have several time records that I would like to group with a query. For shifts 1, 2 and 3, this query works fine ::
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,
This gives me some kind of shift_id. To determine the shift of 4a and 4b, I could extend the case statement, but I'm a little worried about query performance.
Can someone give me some advice on how to create a function that returns shift_id in an efficient way? Thank!
EDIT: Some sample data:
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
source to share
This will give you a change. It works by understanding that this is a mathematical problem, not a database.
There are 21 shifts per week and we create a table variable to store the shift names - you can create a permenant table for that with an index on the Number column.
DATEDIFF
By getting the hours between the time and the date set at 5:00 on Monday, until any of the dates that interest us, and taking modulo this from 24 * 7 (the number of hours in a week), we get a number indicating, in what hour 5:00 has passed on Monday (i.e. shift 0). Divide that by 8 and you get the shift number.
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
Edit
I figured out that the weekend change is 12 hours, not 8. This approach works, but it needs a modification, which I'm not going to do because I'm on a tablet!
Instead of dividing by 8, divide by 4, which means the table variable must go from 0 to 40, and every 8 hour shift takes 2 numbers and every 12 hours takes 3.
source to share