CREATE TABLE tasks (
id INT,
start_date DATE,
ende_date DATE,
name VARCHAR(50),
position INT NULL
);
INSERT INTO tasks (id, start_date, ende_date, name,position) VALUES
(1, '2023-12-08', '2023-12-10', '4711', NULL),
(2, '2023-12-01', '2023-12-07', '0815', NULL),
(3, '2023-12-08', '2023-12-12', '2222', NULL),
(4, '2023-12-13', '2023-12-13', '1111', NULL),
(5, '2023-11-30', '2023-12-14', '3333', NULL);
UPDATE tasks
SET position = (
SELECT COALESCE(MAX(position), 0) + 1
FROM tasks t2
WHERE t2.position IS NOT NULL
AND t2.id <> tasks.id
AND (
(tasks.start_date BETWEEN t2.start_date AND t2.ende_date)
OR (tasks.ende_date BETWEEN t2.start_date AND t2.ende_date)
OR (tasks.start_date <= t2.start_date AND tasks.ende_date >= t2.ende_date)
)
)
WHERE position IS NULL;
Select * from tasks
Telegram: DB developers channel