How to INSERT repeated values like (a,b,c,d,a,b,c,d….) in DB table?

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

enter image description here

Leave a Reply