I try to make work schedule table.
I have a table like:
| shift_starts_dt | shift_type |
|---|---|
| 2022-01-01 08:00:00 | Day |
| 2022-01-01 20:00:00 | Night |
| 2022-01-02 08:00:00 | Day |
| 2022-01-02 20:00:00 | Night |
| 2022-01-03 08:00:00 | Day |
| 2022-01-03 20:00:00 | Night |
| 2022-01-04 08:00:00 | Day |
| 2022-01-04 20:00:00 | Night |
etc.. until the end of the year
I can’t figure out how to add repeated values to table.
I want to add the ‘shift_name’ column that contains ‘A’,’B’,’C’,’D’ (It’s like name for team)
What query should I use to achieve the next result:
| shift_starts_dt | shift_type | shift_name |
|---|---|---|
| 2022-01-01 08:00:00 | Day | ‘A’ |
| 2022-01-01 20:00:00 | Night | ‘B’ |
| 2022-01-02 08:00:00 | Day | ‘C’ |
| 2022-01-02 20:00:00 | Night | ‘D’ |
| 2022-01-03 08:00:00 | Day | ‘A’ |
| 2022-01-03 20:00:00 | Night | ‘B’ |
| 2022-01-04 08:00:00 | Day | ‘C’ |
| 2022-01-04 20:00:00 | Night | ‘D’ |
. . . . . .
>Solution :
Just one option using the modulus of row_number() and choose()
Example
Declare @YourTable Table ([shift_starts_dt] datetime,[shift_type] varchar(50)) Insert Into @YourTable Values
('2022-01-01 08:00:00','Day')
,('2022-01-01 20:00:00','Night')
,('2022-01-02 08:00:00','Day')
,('2022-01-02 20:00:00','Night')
,('2022-01-03 08:00:00','Day')
,('2022-01-03 20:00:00','Night')
,('2022-01-04 08:00:00','Day')
,('2022-01-04 20:00:00','Night')
Select *
,ShiftName = choose((row_number() over (order by [shift_starts_dt]) % 4)+1,'D','A','B','C')
from @YourTable
Results
