SQLize | PHPize | SQLtest

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); WITH MaxPosition AS ( SELECT t1.id, ISNULL(MAX(t2.position) + 1, 1) AS max_position FROM tasks t1 JOIN tasks t2 ON (t2.start_date <= t1.ende_date AND t2.ende_date >= t1.start_date) OR (t2.start_date < t1.ende_date AND t2.ende_date >= t1.start_date) OR (t2.start_date <= t1.ende_date AND t2.ende_date > t1.start_date) OR (t2.ende_date = DATEADD(day, 0, t1.start_date)) OR (t2.start_date = DATEADD(day, 0, t1.ende_date)) GROUP BY t1.id ) UPDATE t1 SET t1.position = max_position FROM tasks t1 JOIN MaxPosition ON t1.id = MaxPosition.id WHERE t1.position IS NULL select * from tasks