By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `employee` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`date_employment` date DEFAULT NULL,
`date_dismissal` date DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `employee` (`id`, `date_employment`, `date_dismissal`) VALUES
(1, '2022-01-01', NULL),
(2, '2022-02-20', NULL),
(3, '2022-03-01', '2022-06-01'),
(4, '2022-04-20', '2022-05-01'),
(5, '2022-05-01', NULL),
(6, '2022-06-01', NULL);
Records: 6 Duplicates: 0 Warnings: 0
select * from employee
| id | date_employment | date_dismissal |
|---|---|---|
| 1 | 2022-01-01 | null |
| 2 | 2022-02-20 | null |
| 3 | 2022-03-01 | 2022-06-01 |
| 4 | 2022-04-20 | 2022-05-01 |
| 5 | 2022-05-01 | null |
| 6 | 2022-06-01 | null |
SET @range_from := '2021-12-05';
SET @range_till := '2022-06-05';
WITH RECURSIVE
cte AS ( SELECT CAST(DATE_FORMAT(@range_from, '%Y-%m-01') AS DATE) month_start,
LAST_DAY(@range_from) month_end
UNION ALL
SELECT month_start + INTERVAL 1 MONTH,
LAST_DAY(month_start + INTERVAL 1 MONTH)
FROM cte
WHERE month_start < DATE_FORMAT(@range_till, '%Y-%m-01')
)
SELECT cte.month_start, COUNT(employee.id) employees_amount
FROM cte
LEFT JOIN employee ON employee.date_employment <= cte.month_end
AND ( employee.date_dismissal >= cte.month_start
OR employee.date_dismissal IS NULL )
GROUP BY 1;
| month_start | employees_amount |
|---|---|
| 2021-12-01 | 0 |
| 2022-01-01 | 1 |
| 2022-02-01 | 2 |
| 2022-03-01 | 3 |
| 2022-04-01 | 4 |
| 2022-05-01 | 5 |
| 2022-06-01 | 5 |