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', 1), (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 ISNULL(MAX(t2.position) + 1, 1) AS max_position FROM tasks t2 WHERE (t2.start_date <= '2023-12-07' AND t2.ende_date >= '2023-12-01') OR (t2.start_date < '2023-12-07' AND t2.ende_date >= '2023-12-01') OR (t2.start_date <= '2023-12-07' AND t2.ende_date > '2023-12-01') OR (t2.ende_date = DATEADD(day, -1, '2023-12-01')) OR (t2.start_date = DATEADD(day, 1, '2023-12-07')) ) UPDATE t1 SET t1.position = max_position FROM tasks t1 CROSS JOIN MaxPosition WHERE t1.id = 2 AND t1.position IS NULL; WITH MaxPosition AS ( SELECT ISNULL(MAX(t2.position) + 1, 1) AS max_position FROM tasks t2 WHERE (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, -1, t1.start_date)) OR (t2.start_date = DATEADD(day, 1, t1.ende_date)) ) UPDATE t1 SET t1.position = max_position FROM tasks t1 CROSS JOIN MaxPosition WHERE t1.id = 3 AND t1.position IS NULL; select * from tasks