Запрос на динамику актуального количества сотрудников помесячно

Question

Есть таблицa Employees с колонками (ID сотрудника, ID подразделения, зарплата, дата начала работы, дата окончания работы , ID руководителя):
        ID       DEPT     SALARY BEGIN_DA END_DATE     MAN_ID
---------- ---------- ---------- -------- -------- ----------
         1         22    3500,86 01.07.19 01.05.20          1
         1         24    2000,86 01.06.19 01.05.20          2
         2         23    3500,86 01.06.19 01.05.20          2
         3         24    3500,86 01.06.19 01.05.20          3
         4         22    2000,86 01.06.19 01.05.20          3
         7         21    3500,86 01.05.19 01.05.20          1

Как посчитать динамику количества сотрудников отделов помесячно в формате:
Отдел,месяц1,месяц2,месяц3, ... итд, за 2019 год?
Пробовал через with и аналитическую функцию: sum() over (partition by).
Но значения получились в формате: Отдел, месяц, колличество.
Попробовал пример с Pivot:
With t1 as ( 
    Select dept, to_char(begin_date,'mmyyyy') mmyyyy, count (Id) cnt 
    from employees where extract (year from begin_date) = 2019 
    group by dept, to_char(begin_date,'mmyyyy')) 
select * from (select dept, cnt, mmyyyy from t1) 
pivot (sum (cnt) for mmyyyy in (
    '012019','022019','032019','042019','052019','062019',
    '072019','082019','092019','102019','112019','122019'));

Но сумма не переходит на след. месяц, т.е. считается количество новых сотрудников в месяц по отделам, а необходимо отследить динамику. Динамика - это актуальное количество сотрудников в отделе помесячно.

0xdb · Answer

Сделал бы так:
select * from (
    select 
        dept, to_char (m.fday, 'mmyyyy') as mmyyyy, 
        count (case when e.begin_date <= m.fday and m.fday < e.end_date then 1 end) emps
    from employees e 
    cross join (
        select add_months (date'2019-01-01', level-1) fday 
        from dual connect by level <= 12) m 
    group by dept, to_char (m.fday, 'mmyyyy'))
pivot (sum (emps) for mmyyyy in (
    '012019' as "012019",'022019' as "022019",'032019' as "032019",
    '042019' as "042019",'052019' as "052019",'062019' as "062019",
    '072019' as "072019",'082019' as "082019",'092019' as "092019",
    '102019' as "102019",'112019' as "112019",'122019' as "122019"))
/

Результат как и ожидался:
DEPT 012019 022019 032019 042019 052019 062019 072019 082019 092019 102019 112019 122019
---- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
  22      0      0      0      0      0      1      2      2      2      2      2      2
  21      0      0      0      0      1      1      1      1      1      1      1      1
  23      0      0      0      0      0      1      1      1      1      1      1      1
  24      0      0      0      0      0      2      2      2      2      2      2      2

Запрос и исходные данные на db<>fiddle.

Aziz Umarov · Answer

Идея очень проста нужно лишь правильно суммировать по месяцам, идеи можно черпать отсюда

SELECT DEP,  
 SUM(CASE WHEN BEGIN_DATE < Date('2019-02-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-01-01')) THEN 1 ELSE 0 END) AS "01.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-03-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-02-01')) THEN 1 ELSE 0 END) AS "02.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-04-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-03-01')) THEN 1 ELSE 0 END) AS "03.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-05-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-04-01')) THEN 1 ELSE 0 END) AS "04.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-06-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-05-01')) THEN 1 ELSE 0 END) AS "05.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-07-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-06-01')) THEN 1 ELSE 0 END) AS "06.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-08-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-07-01')) THEN 1 ELSE 0 END) AS "07.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-09-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-08-01')) THEN 1 ELSE 0 END) AS "08.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-10-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-09-01')) THEN 1 ELSE 0 END) AS "09.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-11-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-10-01')) THEN 1 ELSE 0 END) AS "10.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2019-12-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-11-01')) THEN 1 ELSE 0 END) AS "11.2019",
 SUM(CASE WHEN BEGIN_DATE < Date('2020-01-01') AND (END_DATE IS NULL OR END_DATE >= Date('2019-12-01')) THEN 1 ELSE 0 END) AS "12.2019"
FROM Employees
WHERE
    EXTRACT( year FROM BEGIN_DATE) < 2020 -- устроился до 2020 года, можно исключить это условие
     AND (END_DATE IS NULL -- работает до сих пор
          OR EXTRACT( year from END_DATE) > 2018) -- уволился до 2019
GROUP BY 
DEP

Я не стал добавлять начальника отдела так как в течении года может смениться он.

Запрос на динамику актуального количества сотрудников помесячно

2 Answers

Add your own answers!

Ask a Question