CODEWITHSUNDEEP
sqlt-sqldatetime
Koen VC
Koen VC

Reputation: 47

Group time stamps by shift

In our factory, we have following shifts:

In a certain table we have several date time entries which I'd like to group using a query. For shift 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 identify shift 4a and 4b I could extend the case statement but I'm a bit worried about the performance of the query.

Can anyone give me a tip how to create a function that returns a shift_id in an efficient way? Thank you!

EDIT: Some example 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

Upvotes: 3

Views: 2309

Answers (1)

Dale M
Dale M

Reputation: 2473

This will give you the shifts. It works by realising that this is a mathematical problem, not a database one.

There are 21 shifts per week and we create a table variable to hold the shift names - you may want to create a permenant table for this with an index on the Number column.

By getting a DATEDIFF by hours between the time and datetime that is set to 5:00 on a monday before any of the dates we are interested in and taking the modulo of this with 24*7 (the number of hours in a week) we get a number indicating what hour it is past 5:00 on Monday (i.e. shift 0). Divide this 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 realised that the weekend shifts are 12 hours not 8. This approach works but will need modification which I'm not going to do because I'm on a tablet!

Instead of dividing by 8, divide by 4, this means the table variable has to go from 0 to 40 and each 8 hour shift takes 2 numbers and each 12 hour takes 3.

Upvotes: 1

Related Questions