How to get current employee counts as of every date in the last 5 years-CodePudding

--PL/SQL

I want two resulting columns. One column is every date in the last 5 years and the other column is the employee count as of each date.

I have a query below where you enter a date as a parameter and it tells you how many people are employed on that date but I don't know how to extrapolate it to achieve my goal above. Achieving my goal would make the parameter unnecessary so I am looking to get rid of it.

Please help, I'm stumped!

select count(person_id), :EFF_DATE
from
(
   select paa.person_id
   from apps.per_all_assignments_f paa --employee assignments
     ,apps.per_assignment_status_types past --assignment statuses
  
   where paa.assignment_status_type_id = past.assignment_status_type_id
   and past.user_status in ('Active Assignment','Transitional - Active','Transitional - 
       Inactive','Sabbatical','Sabbatical 50%')
   and :EFF_DATE between paa.effective_start_date and paa.effective_end_date

   group by paa.person_id
)

CodePudding user response：

If I understood you correctly, you need to create a calendar which contains all dates in last 5 years, and then join it to tables you currently use in that query.

Something like this (untested, as I don't have your tables nor data):

WITH
   calendar (datum)
   AS
      -- last 5 years
      (    SELECT TRUNC (SYSDATE) - LEVEL   1
             FROM DUAL
       CONNECT BY LEVEL <=
                  TRUNC (SYSDATE) - ADD_MONTHS (TRUNC (SYSDATE), -12 * 5))
  SELECT c.datum, COUNT (DISTINCT paa.person_id) cnt
    FROM calendar c
         JOIN apps.per_all_assignment_f paa
            ON c.datum BETWEEN paa.effective_start_date
                           AND paa.effective_end_date
         JOIN apps.per_assignment_status_types past
            ON past.assignment_status_type_id = paa.assignment_status_type_id
   WHERE past.user_status IN ('Active Assignment',
                              'Transitional - Active',
                              'Transitional - Inactive',
                              'Sabbatical',
                              'Sabbatical 50%')
GROUP BY c.datum
ORDER BY c.datum;