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 GanttChart AS (
SELECT
t.id AS task_id,
DENSE_RANK() OVER (ORDER BY t.start_date) AS row_num,
t.start_date AS start_date,
t.ende_date AS end_date,
t.name AS task_name,
t.position
FROM tasks t
WHERE NOT EXISTS (
SELECT 1
FROM tasks t2
WHERE t2.id <> t.id
AND t2.start_date <= t.ende_date
AND t2.ende_date >= t.start_date
)
UNION ALL
SELECT
t.id AS task_id,
gc.row_num,
CASE WHEN gc.end_date >= t.start_date THEN gc.end_date ELSE t.start_date END AS start_date,
t.ende_date AS end_date,
t.name AS task_name,
t.position
FROM GanttChart gc
JOIN tasks t ON gc.task_id = t.id
WHERE NOT EXISTS (
SELECT 1
FROM GanttChart gc2
WHERE gc2.row_num = gc.row_num
AND gc2.task_id <> t.id
AND gc2.start_date <= t.ende_date
AND gc2.end_date >= t.start_date
)
)
SELECT
row_num,
task_id,
start_date,
end_date,
task_name,
position
FROM GanttChart
ORDER BY row_num, start_date, task_id;
Telegram: DB developers channel