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 |