By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table progetti (
id int,
start_date date,
end_date date
);
insert into progetti values
(1, '2022-01-01', '2023-06-30'),
(2, '2022-01-01', '2022-12-31');
Records: 2 Duplicates: 0 Warnings: 0
create table s (
period varchar(20),
total int
);
insert into s values
('03-2022', 2),
('04-2022', 1),
('05-2022', 3),
('06-2022', 2);
Records: 4 Duplicates: 0 Warnings: 0
WITH RECURSIVE dates AS
(
SELECT start_date as period, end_date
FROM progetti
WHERE id = 1
UNION ALL
SELECT DATE_ADD(period, INTERVAL 1 MONTH), end_date
FROM dates
WHERE DATE_ADD(period, INTERVAL 1 MONTH) < end_date
)
SELECT DATE_FORMAT(d.period, '%m-%Y') AS period, COALESCE(total, 0) AS total
FROM dates d
LEFT JOIN s on s.period = DATE_FORMAT(d.period, '%m-%Y')
| period | total |
|---|---|
| 01-2022 | 0 |
| 02-2022 | 0 |
| 03-2022 | 2 |
| 04-2022 | 1 |
| 05-2022 | 3 |
| 06-2022 | 2 |
| 07-2022 | 0 |
| 08-2022 | 0 |
| 09-2022 | 0 |
| 10-2022 | 0 |
| 11-2022 | 0 |
| 12-2022 | 0 |
| 01-2023 | 0 |
| 02-2023 | 0 |
| 03-2023 | 0 |
| 04-2023 | 0 |
| 05-2023 | 0 |
| 06-2023 | 0 |