Total Count of Active Employees by Date

Question

I have in the past written queries that give me counts by date (hires, terminations, etc...) as follows:

SELECT per.date_start AS "Date",
  COUNT(peo.EMPLOYEE_NUMBER) AS "Hires"

FROM hr.per_all_people_f peo,
  hr.per_periods_of_service per

WHERE per.date_start BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE

AND per.date_start BETWEEN :PerStart AND :PerEnd

AND per.person_id = peo.person_id

GROUP BY per.date_start

I was now looking to create a count of active employees by date, however I am not sure how I would date the query as I use a range to determine active as such:

SELECT COUNT(peo.EMPLOYEE_NUMBER) AS "CT"

FROM hr.per_all_people_f peo

WHERE peo.current_employee_flag = 'Y'

and TRUNC(sysdate) BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE

Answer 1

I was able to get the results I was looking for with the following:

--Active Team Members by Date
SELECT "a_date",
  COUNT(peo.EMPLOYEE_NUMBER) AS "CT"
FROM hr.per_all_people_f peo,
  (SELECT DATE '2012-04-01'-1 + LEVEL AS "a_date"
  FROM dual
    CONNECT BY LEVEL <= DATE '2012-04-30'+2 - DATE '2012-04-01'-1
  )
WHERE peo.current_employee_flag = 'Y'
AND "a_date" BETWEEN peo.effective_start_date AND peo.EFFECTIVE_END_DATE
GROUP BY "a_date"
ORDER BY "a_date"

Answer 2

Here is my example based on Gordon Linoff answer with a little modification, because in SUBSTRACT table all records were appeared with -1 in NUM, even if no date was in END DATE = NULL.

use AdventureWorksDW2012  --using in MS SSMS for choosing DATABASE to work with
                          -- and may be not work in other platforms

select
    t.thedate
    ,max(t.numActives) AS "Total Active Employees"
from (
        select
            dates.thedate
            ,SUM(dates.num) over (order by dates.thedate) as numActives
        from
            (
                (
                select
                    StartDate as thedate
                    ,1 as num
                from DimEmployee
                )
                    union all
                        (
                        select
                            EndDate as thedate
                            ,-1 as num
                        from DimEmployee
                        where EndDate IS NOT NULL
                        )
            ) AS dates
     ) AS t
group by thedate
ORDER BY thedate

worked for me, hope it will help somebody

Answer 3

If you want to count all employees who were active during the entire input date range

SELECT COUNT(peo.EMPLOYEE_NUMBER) AS "CT"
FROM hr.per_all_people_f peo
WHERE peo.[EFFECTIVE_START_DATE] <= :StartDate   
AND  (peo.[EFFECTIVE_END_DATE] IS NULL OR peo.[EFFECTIVE_END_DATE] >= :EndDate)  

Answer 4

Here is a simple way to get started. This works for all the effective and end dates in your data:

select thedate,
       SUM(num) over (order by thedate) as numActives
from ((select effective_start_date as thedate, 1 as num from hr.per_periods_of_service) union all
      (select effective_end_date as thedate, -1 as num from hr.per_periods_of_service)
     ) dates

It works by adding one person for each start and subtracting one for each end (via num) and doing a cumulative sum. This might have duplicates dates, so you might also do an aggregation to eliminate those duplicates:

select thedate, max(numActives)
from (select thedate,
             SUM(num) over (order by thedate) as numActives
      from ((select effective_start_date as thedate, 1 as num from hr.per_periods_of_service) union all
            (select effective_end_date as thedate, -1 as num from hr.per_periods_of_service)
           ) dates
     ) t
group by thedate;

If you really want all dates, then it is best to start with a calendar table, and use a simple variation on your original query:

select c.thedate, count(*) as NumActives
from calendar c left outer join
     hr.per_periods_of_service pos
     on c.thedate between pos.effective_start_date and pos.effective_end_date
group by c.thedate;