Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE sorts (SortID, Name) AS
SELECT 0, 'A' FROM DUAL UNION ALL
SELECT 1, 'B' FROM DUAL UNION ALL
SELECT 2, 'C' FROM DUAL UNION ALL
SELECT 3, 'D' FROM DUAL;
4 rows affected

SELECT d.dt,
       d.week,
       MOD(d.week + s.sortid, s.num_sorts) AS mission,
       s.name
FROM   (
         SELECT DATE '2021-01-02' + LEVEL AS dt,
                CEIL(LEVEL / 7) AS week
         FROM   DUAL
         CONNECT BY LEVEL <= 9
       ) d
       CROSS JOIN (
         SELECT sortid,
                name,
                COUNT(*) OVER() AS num_sorts
         FROM   sorts
       ) s


DT
WEEK
MISSION
NAME


03-JAN-21
1
1
A

03-JAN-21
1
2
B

03-JAN-21
1
3
C

03-JAN-21
1
0
D

04-JAN-21
1
1
A

04-JAN-21
1
2
B

04-JAN-21
1
3
C

04-JAN-21
1
0
D

05-JAN-21
1
1
A

05-JAN-21
1
2
B

05-JAN-21
1
3
C

05-JAN-21
1
0
D

06-JAN-21
1
1
A

06-JAN-21
1
2
B

06-JAN-21
1
3
C

06-JAN-21
1
0
D

07-JAN-21
1
1
A

07-JAN-21
1
2
B

07-JAN-21
1
3
C

07-JAN-21
1
0
D

08-JAN-21
1
1
A

08-JAN-21
1
2
B

08-JAN-21
1
3
C

08-JAN-21
1
0
D

09-JAN-21
1
1
A

09-JAN-21
1
2
B

09-JAN-21
1
3
C

09-JAN-21
1
0
D

10-JAN-21
2
2
A

10-JAN-21
2
3
B

10-JAN-21
2
0
C

10-JAN-21
2
1
D

11-JAN-21
2
2
A

11-JAN-21
2
3
B

11-JAN-21
2
0
C

11-JAN-21
2
1
D


SELECT d.dt,
       d.week,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 0
         THEN s.name
         END
       ) AS missionA,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 1
         THEN s.name
         END
       ) AS missionB,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 2
         THEN s.name
         END
       ) AS missionC,
       MAX(
         CASE MOD(4 + MOD(3 + s.sortid - d.week, 4), 4)
         WHEN 3
         THEN s.name
         END
       ) AS missionD
FROM   (
         SELECT DATE '2021-01-02' + LEVEL AS dt,
                CEIL(LEVEL / 7) AS week
         FROM   DUAL
         CONNECT BY LEVEL <= 9
       ) d
       CROSS JOIN sorts s
GROUP BY dt, week
ORDER BY dt


DT
WEEK
MISSIONA
MISSIONB
MISSIONC
MISSIOND


03-JAN-21
1
C
D
A
B

04-JAN-21
1
C
D
A
B

05-JAN-21
1
C
D
A
B

06-JAN-21
1
C
D
A
B

07-JAN-21
1
C
D
A
B

08-JAN-21
1
C
D
A
B

09-JAN-21
1
C
D
A
B

10-JAN-21
2
D
A
B
C

11-JAN-21
2
D
A
B
C

DT	WEEK	MISSION	NAME
03-JAN-21	1	1	A
03-JAN-21	1	2	B
03-JAN-21	1	3	C
03-JAN-21	1	0	D
04-JAN-21	1	1	A
04-JAN-21	1	2	B
04-JAN-21	1	3	C
04-JAN-21	1	0	D
05-JAN-21	1	1	A
05-JAN-21	1	2	B
05-JAN-21	1	3	C
05-JAN-21	1	0	D
06-JAN-21	1	1	A
06-JAN-21	1	2	B
06-JAN-21	1	3	C
06-JAN-21	1	0	D
07-JAN-21	1	1	A
07-JAN-21	1	2	B
07-JAN-21	1	3	C
07-JAN-21	1	0	D
08-JAN-21	1	1	A
08-JAN-21	1	2	B
08-JAN-21	1	3	C
08-JAN-21	1	0	D
09-JAN-21	1	1	A
09-JAN-21	1	2	B
09-JAN-21	1	3	C
09-JAN-21	1	0	D
10-JAN-21	2	2	A
10-JAN-21	2	3	B
10-JAN-21	2	0	C
10-JAN-21	2	1	D
11-JAN-21	2	2	A
11-JAN-21	2	3	B
11-JAN-21	2	0	C
11-JAN-21	2	1	D