Group By Month between two dates-CodePudding

I have a table that lists all employees and their respective start and end dates

I want to be able to count the number of active employees in each month. Is there a way to do this via a single query (eg groupBy) rather than generating multiple queries for each month?

    =================================
    Employee ID   | StartDt      | EndDt
    123           | 01 Feb 2022  |  
    234           | 01 jan 2022  | 28 Feb 2022
    456           | 01 dec 2021  | 28 Feb 2022

As an example, the table above should return:

Dec 2021: 1
Jan 2022: 2
Feb 2022: 3
Mar 2022: 1
Apr 2022: 1

CodePudding user response：

You can generate a calendar and join to that:

WITH calendar (month) AS (
  SELECT ADD_MONTHS(min_startdt, LEVEL - 1)
  FROM   (
    SELECT MIN(startdt) AS min_startdt,
           MAX(COALESCE(enddt, SYSDATE)) AS max_enddt
    FROM   employees
  )
  CONNECT BY LEVEL - 1 <= MONTHS_BETWEEN(max_enddt, min_startdt)
)
SELECT c.month,
       COUNT(e.employee_id)
FROM   calendar c
       LEFT OUTER JOIN employees e
       ON (e.startdt <= c.month AND (c.month <= e.enddt OR e.enddt IS NULL))
GROUP BY
       c.month

Which, for the sample data:

CREATE TABLE employees (Employee_ID, StartDt, EndDt ) AS
SELECT 123, DATE '2022-02-01', NULL FROM DUAL UNION ALL
SELECT 234, DATE '2022-01-01', DATE '2022-02-28' FROM DUAL UNION ALL
SELECT 456, DATE '2021-12-01', DATE '2022-02-28' FROM DUAL;

Outputs:

MONTH COUNT(E.EMPLOYEE_ID)

2021-12-01 00:00:00 1

2022-01-01 00:00:00 2

2022-02-01 00:00:00 3

2022-03-01 00:00:00 1

2022-04-01 00:00:00 1

MONTH	COUNT(E.EMPLOYEE_ID)
2021-12-01 00:00:00	1
2022-01-01 00:00:00	2
2022-02-01 00:00:00	3
2022-03-01 00:00:00	1
2022-04-01 00:00:00	1

db<>fiddle here