I work in a Factory that makes Components for Trucks.
We manufacture these components over 4 Rotating Shifts – named A,B,C and D. Each Shift does 2 Weeks of Days followed by 2 Weeks of Nights. (So the whole pattern repeats for all 4 Shifts after 28 Days)
Days are from 7am-7pm (6:59pm) and Nights are from 7pm until 7am (6:59am) the next Morning.
This Shift Pattern started in December 2019 (shown below) and is anticipated to continue for the foreseeable future.
| WEEKDAY | DATE | DAYS | NIGHTS | DATE | DAYS | NIGHTS | DATE | DAYS | NIGHTS | DATE | DAYS | NIGHTS | |||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Monday | 02DEC | A | C | - | 09DEC | B | D | - | 16DEC | C | A | - | 23DEC | D | B |
| Tuesday | 03DEC | A | C | - | 10DEC | B | D | - | 17DEC | C | A | - | 24DEC | D | B |
| Wednesday | 04DEC | D | B | - | 11DEC | A | C | - | 18DEC | B | D | - | 25DEC | C | A |
| Thursday | 05DEC | D | B | - | 12DEC | A | C | - | 19DEC | B | D | - | 26DEC | C | A |
| Friday | 06DEC | A | C | - | 13DEC | B | D | - | 20DEC | C | A | - | 27DEC | D | B |
| Saturday | 07DEC | A | C | - | 14DEC | B | D | - | 21DEC | C | A | - | 28DEC | D | B |
| Sunday | 08DEC | A | C | - | 15DEC | B | D | - | 22DEC | C | A | - | 29DEC | D | B |
The information related to said components manufacture is fed into an AWS Database (Amazon Redshift) that I access with SQL Workbench.
The table that I am currently looking at is called RS_MACHINESTATUS and one of the Columns of this table is called RUN_TIMESTAMP and contains Date-Times in the format of '13-May-2019 23:09:30’.
My Question is – is it possible to create a adjacent Results Column that provides the Shift based on the corresponding timestamp entry in the RUN_TIMESTAMP Column?
If we take the December 2019 Pattern above - my Desired Result would be Something like:
| RUN_TIMESTAMP | SHIFT |
|---|---|
| 02DEC19 13:05:45 | A |
| 04DEC19 20:05:34 | B |
| 12DEC19 03:03:23 | C |
You could define your shift pattern data just once because that pattern repeats every 4 weeks (28 days)...
MySQL code
-- S a m p l e D a t a :
WITH
shift_pattern (WEEKDAY, DAY_ID, DAYS, NIGHTS) AS
( Select DayName(DATE '2019-12-02'), DATE '2019-12-02', 'A', 'C' From Dual Union All
Select DayName(DATE '2019-12-03'), DATE '2019-12-03', 'A', 'C' From Dual Union All
Select DayName(DATE '2019-12-04'), DATE '2019-12-04', 'D', 'B' From Dual Union All
Select DayName(DATE '2019-12-05'), DATE '2019-12-05', 'D', 'B' From Dual Union All
Select DayName(DATE '2019-12-06'), DATE '2019-12-06', 'A', 'C' From Dual Union All
Select DayName(DATE '2019-12-07'), DATE '2019-12-07', 'A', 'C' From Dual Union All
Select DayName(DATE '2019-12-08'), DATE '2019-12-08', 'A', 'C' From Dual Union All
--
Select DayName(DATE '2019-12-09'), DATE '2019-12-09', 'B', 'D' From Dual Union All
Select DayName(DATE '2019-12-10'), DATE '2019-12-10', 'B', 'D' From Dual Union All
Select DayName(DATE '2019-12-11'), DATE '2019-12-11', 'A', 'C' From Dual Union All
Select DayName(DATE '2019-12-12'), DATE '2019-12-12', 'A', 'C' From Dual Union All
Select DayName(DATE '2019-12-13'), DATE '2019-12-13', 'B', 'D' From Dual Union All
Select DayName(DATE '2019-12-14'), DATE '2019-12-14', 'B', 'D' From Dual Union All
Select DayName(DATE '2019-12-15'), DATE '2019-12-15', 'B', 'D' From Dual Union All
--
Select DayName(DATE '2019-12-16'), DATE '2019-12-16', 'C', 'A' From Dual Union All
Select DayName(DATE '2019-12-17'), DATE '2019-12-17', 'C', 'A' From Dual Union All
Select DayName(DATE '2019-12-18'), DATE '2019-12-18', 'B', 'D' From Dual Union All
Select DayName(DATE '2019-12-19'), DATE '2019-12-19', 'B', 'D' From Dual Union All
Select DayName(DATE '2019-12-20'), DATE '2019-12-20', 'C', 'A' From Dual Union All
Select DayName(DATE '2019-12-21'), DATE '2019-12-21', 'C', 'A' From Dual Union All
Select DayName(DATE '2019-12-22'), DATE '2019-12-22', 'C', 'A' From Dual Union All
--
Select DayName(DATE '2019-12-23'), DATE '2019-12-23', 'D', 'B' From Dual Union All
Select DayName(DATE '2019-12-24'), DATE '2019-12-24', 'D', 'B' From Dual Union All
Select DayName(DATE '2019-12-25'), DATE '2019-12-25', 'C', 'A' From Dual Union All
Select DayName(DATE '2019-12-26'), DATE '2019-12-26', 'C', 'A' From Dual Union All
Select DayName(DATE '2019-12-27'), DATE '2019-12-27', 'D', 'B' From Dual Union All
Select DayName(DATE '2019-12-28'), DATE '2019-12-28', 'D', 'B' From Dual Union All
Select DayName(DATE '2019-12-29'), DATE '2019-12-29', 'D', 'B' From Dual
)
... with some data like in your RS_MACHINESTATUS table
-- S a m p l e D a t a :
/*
ID TIME_STAMP
-- -------------------
1 2019-12-02 13:05:45
2 2019-12-04 20:05:34
3 2019-12-12 03:03:23
4 2020-01-19 07:00:00
5 2023-10-05 19:00:00 */
Having data from above you can use the fact that the same pattern is repeating every 28 dates.
Join shift pattern and RS_MACHINESTATUS by the date difference that is multiplyer of 28 days and you could get the matching pattern row with day and night shifts. Check if hour of TIME_STAMP is day or night and select the matching shift.
-- S Q L :
Select ms.ID, ms.TIME_STAMP, Case When HOUR(ms.TIME_STAMP) BETWEEN 7 And 18
Then sp.DAYS
Else sp.NIGHTS
End as ACTIVE_SHIFT
From shift_pattern sp
Inner Join RS_MACHINESTATUS ms ON(1 = 1)
Where Mod(DateDiff( Case When HOUR(ms.TIME_STAMP) BETWEEN 7 And 18
Then DATE(ms.TIME_STAMP)
Else DATE(DATE_SUB(ms.TIME_STAMP, INTERVAL '7' HOUR))
-- above Else -> is a 7 hour shift in time to get the right date of the day when NIGHT shift started
End,
sp.DAY_ID), 28) = 0
Order By ms.ID;
/* R e s u l t :
ID TIME_STAMP ACTIVE_SHIFT
-- ------------------- --------------
1 2019-12-02 13:05:45 A
2 2019-12-04 20:05:34 B
3 2019-12-12 03:03:23 C
4 2020-01-19 07:00:00 C
5 2023-10-05 19:00:00 B */
Below is the code that show matching shift pattern rows:
-- S Q L 2 :
Select ms.ID, ms.TIME_STAMP, Case When HOUR(ms.TIME_STAMP) BETWEEN 7 And 18
Then sp.DAYS
Else sp.NIGHTS
End as ACTIVE_SHIFT,
--
Concat(
Concat('Active shift is ', Case When HOUR(ms.TIME_STAMP) BETWEEN 7 And 18
Then 'DAY '
Else 'NIGHT'
End),
' shift from patern data >>>> ') as COMMENT,
--
sp.WEEKDAY, sp.DAY_ID, sp.DAYS, sp.NIGHTS
From shift_pattern sp
Inner Join RS_MACHINESTATUS ms ON(1 = 1)
Where Mod(DateDiff( Case When HOUR(ms.TIME_STAMP) BETWEEN 7 And 18
Then DATE(ms.TIME_STAMP)
Else DATE(DATE_SUB(ms.TIME_STAMP, INTERVAL '7' HOUR))
End,
sp.DAY_ID), 28) = 0
Order By ms.ID;
/* R e s u l t :
ID TIME_STAMP ACTIVE_SHIFT COMMENT WEEKDAY DAY_ID DAYS NIGHTS
-- ------------------- ------------ --------------------------------------------------- ---------- ---------- ----- ------
1 2019-12-02 13:05:45 A Active shift is DAY shift from patern data >>>> Monday 2019-12-02 A C
2 2019-12-04 20:05:34 B Active shift is NIGHT shift from patern data >>>> Wednesday 2019-12-04 D B
3 2019-12-12 03:03:23 C Active shift is NIGHT shift from patern data >>>> Wednesday 2019-12-11 A C
4 2020-01-19 07:00:00 C Active shift is DAY shift from patern data >>>> Sunday 2019-12-22 C A
5 2023-10-05 19:00:00 B Active shift is NIGHT shift from patern data >>>> Thursday 2019-12-05 D B */
NOTE: It should give youu expected result even for TIME_STAMPs before/after the shift pattern period (ID 4 and 5) as long as the pattern repeats as stated in the question...