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 DateSeries AS (
SELECT
task_id,
DATEADD(DAY, n, start_date) AS calendar_date
FROM (
SELECT
id AS task_id,
start_date,
DATEDIFF(DAY, start_date, ende_date) AS date_range
FROM tasks
) t
CROSS APPLY (SELECT TOP (t.date_range + 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n FROM master.dbo.spt_values) AS numbers
)
SELECT
ds.task_id AS id,
ds.calendar_date,
t.name AS task_name,
t.position
FROM DateSeries ds
JOIN tasks t ON ds.task_id = t.id
ORDER BY ds.task_id, ds.calendar_date;
Telegram: DB developers channel