Как посчитать количество сотрудников в штате за каждый месяц в каждом году?

Question

[long_skinny_boy]

Добрый день, имеется задача в подсчете количества сотрудников в каждом месяце когда, каждого года
Если упростить таблицу с данными то она состоит из 3х столбцов: ID сотрудника, дата приема, дата увольнения

https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=65c...

Получается нужно как то хитро сгруппировать таким образом, чтобы получается на выходе получилось что то подобное

2022-01-01 - 1
2022-02-01 - 2
2022-03-01 - 3
2022-04-01 - 4
2022-05-01 - 5
2022-06-01 - 5

Т.е. без разницы в какой день месяца сотрудник оформился/уволился этот месяц считается для него в работе
например если оформился 2022-01-31 а уволился 2022-02-01 - то он всё равно должен учавствовать в счётчике за оба месяца, т.к. хотя бы один день был в каждом месяце

Comments

[Beliyadm]

GROUP BY EXTRACT(MONTH FROM date_field)
должно работать

[Akina]

Fiddle кривой. Сотрудник, принятый 1 марта и уволенный 3 января - явный косяк. И второй уволенный - не лучше.

[Akina]

Beliyadm, и как это должно учитывать увольнения?

[Beliyadm]

Akina, никак, такая группировка покажет человека и в первом месяце и в втором, если даже работал 2 дня (один в январе один в феврале), как вроде и требуется

[long_skinny_boy]

Akina, прошу прощения, не заметил, поправил

[long_skinny_boy]

Beliyadm, но ведь таким образом не учитывается дата увольнения, т.е. сотрудник если уже уволился он всё равно за сегодняшний например месяц будет подсчитываться

[Beliyadm]

long_skinny_boy, Так вы же именно этого и хотите, цитирую

без разницы в какой день месяца сотрудник оформился/уволился этот месяц считается для него в работе

[Akina]

Beliyadm, В таблице вообще-то два разных date_field... по какому группировать-то?

[long_skinny_boy]

Beliyadm, я наверно не точно описал, имел ввиду что если сотрудник оформился 31 января - он должен учитываться в подсчете за январь, хоть и проработал один день, и соответственно если он на следующий день уволился 1 февраля, то тоже должен учитываться, т.е. в обоих месяц как будто работал полностью, хотя при этом отработал всего 2 дня

Answer 1

[Akina]

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;

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=8be...

Comments

[long_skinny_boy]

Правильно понимаю, что в cte вы создаёте некий перечень месяцев за все используемые года, и потом к ним джойнить по условию 2ух дат, чтобы получить в результате выборку по месяцу каждого года с приджойненным каждым сотрудникам (их количеству)

это я теорию пытаюсь понять

[Akina]

long_skinny_boy,

Да, в CTE я генерирую все месяцы в требуемом диапазоне. Брать их из таблицы опасно - вдруг в каком-то месяце не было движения? тогда этот месяц выпадет из отчёта... а так я гарантирую, что все месяцы будут присутствовать.

Ну а в основном запросе я просто связываю рабочую таблицу с календарём по пересечению диапазонов с поправкой на NULL.

PS. Внёс изменение. Некритичное - ибо вряд ли попадётся период вообще без работников,- но для академической строгости надо.

[long_skinny_boy]

Akina, как всё просто оказывается может быть! =)
тут единственное похоже, конечная дата должна указываться первый день месяца, т.е. не прокатит конечной датой указать CURDATE(), заскакивает на следующий месяц от текущего

Низкий поклон за помощь!

[Akina]

long_skinny_boy,

заскакивает на следующий месяц от текущего

Ну уж это-то мог бы и сам поправить... ок, откорректировал.