DECLARE @Ashift DATETIME;
SET @Ashift = '2019-01-01 08:00';
WITH Shifts AS (
SELECT DISTINCT
CASE
WHEN ((DATEDIFF(HH,@Ashift,GETDATE()) / 24) % 3) + 1 = 1 THEN 'A'
WHEN ((DATEDIFF(HH,@Ashift,GETDATE()) / 24) % 3) + 1 = 2 THEN 'B'
WHEN ((DATEDIFF(HH,@Ashift,GETDATE()) / 24) % 3) + 1 = 3 THEN 'C'
END AS [Shift]
)
SELECT *
FROM Shifts
WHERE [Shift] IS NOT NULL
I have the above SQL for designating a letter to the corresponding day on a 3 day rotation. Is it possible to do something similar for 4 letters where 2 letters occur on one day (A) 0700-1900/ (B)1900-0700 and the other 2 occur another day (C) 0700-1900/ (D)1900-0700. The only problem is these 4 letters would need to repeat on a 2 on 2 off 3 on 2 off 2 on 3 off rotation. Below I've written out a full two weeks of how the letters would alternate. Times are depicted in military time. Thank you!
10/19/2020 0700-1900 = D
10/19/2020 1900-0700 = C
10/20/2020 0700-1900 = D
10/20/2020 1900-0700 = C
10/21/2020 0700-1900 = B
10/21/2020 1900-0700 = A
10/22/2020 0700-1900 = B
10/22/2020 1900-0700 = A
10/23/2020 0700-1900 = D
10/23/2020 1900-0700 = C
10/24/2020 0700-1900 = D
10/24/2020 1900-0700 = C
10/25/2020 0700-1900 = D
10/25/2020 1900-0700 = C
10/26/2020 0700-1900 = B
10/26/2020 1900-0700 = A
10/27/2020 0700-1900 = B
10/27/2020 1900-0700 = A
10/28/2020 0700-1900 = D
10/28/2020 1900-0700 = C
10/29/2020 0700-1900 = D
10/29/2020 1900-0700 = C
10/30/2020 0700-1900 = B
10/30/2020 1900-0700 = A
10/31/2020 0700-1900 = B
10/31/2020 1900-0700 = A
11/01/2020 0700-1900 = B
11/01/2020 1900-0700 = A

