CODEWITHSUNDEEP
sqlpostgresql
Anuj Jain
Anuj Jain

Reputation: 315

Retrieving active employees by month in Postgres

In my employee database, I have a column hire_date which has the hiring date of an employee, and deactivate_date which has the date on which the employee was dismissed (or null if employee is still active).

To find the number of active employees at the beginning of any month, I can run the following query. For example, to see my active employees on 1st January 2019 -

SELECT count(*) 
FROM employees
WHERE hire_date <= '2019-01-01' AND
(deactivate_date IS NULL OR deactivate_date > '2019-01-01')

Now, what I would like to know is the number of active employees on the 1st of every month of 2018. I can obviously run this query 12 times, but would like to know if there is a more efficient solution possible. It seems like the CROSSTAB and generate_series functions of pg will be useful, but I haven't been able to form the proper query.

Upvotes: 1

Views: 413

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Use generate_series():

SELECT gs.dte, count(e.hire_date) 
FROM generate_series('2018-01-01'::date, '2018-12-01'::date, interval '1 month') gs(dte) LEFT JOIN
     employees e
     ON e.hire_date <= gs.dte AND
        (e.deactivate_date IS NULL OR e.deactivate_date > gs.dte)
GROUP BY gs.dte
ORDER BY gs.dte;

Upvotes: 2

Related Questions