SQL to Find Number of Weeks an Employee Was Active Between Two Dates

Question

I have a table with a list of dates where an employee became Active/Inactive and I want to count the weeks that an employee was Active within a certain date range.

So the table (ps_job) would have values like this:

EMPLID     EFFDT       HR_STATUS
------     -----       ------
1000       01-Jul-11   A
1000       01-Sep-11   I
1000       01-Jan-12   A
1000       01-Mar-12   I
1000       01-Sep-12   A

The query would need to show me the number of weeks that this emplid was active from 01-Jul-11 to 31-Dec-12.

The desired result set would be:

EMPLID     WEEKS_ACTIVE
------     ------------
1000       35

I got the number 35 by adding the results from the SQLs below:

SELECT (NEXT_DAY('01-Sep-11','SUNDAY') - NEXT_DAY('01-Jul-11','SUNDAY'))/7 WEEKS_ACTIVE FROM DUAL;
SELECT (NEXT_DAY('01-Mar-12','SUNDAY') - NEXT_DAY('01-Jan-12','SUNDAY'))/7 WEEKS_ACTIVE FROM DUAL;
SELECT (NEXT_DAY('31-Dec-12','SUNDAY') - NEXT_DAY('01-Sep-12','SUNDAY'))/7 WEEKS_ACTIVE FROM DUAL;

The problem is I can't seem to figure out how to create a single query statement that will go through all the rows for every employee within a certain date range and just return each emplid and the number of weeks they were active. I would prefer to use basic SQL instead of PL/SQL so that I can transfer it to a PeopleSoft query that can be run by the user, but I am willing to run it for the user using Oracle SQL Developer if need be.

Database: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Answer 1

Here I'm using lead in a subquery to get the next date and then summing the intervals in the outer query:

with q as (
    select EMPLID, EFFDT, HR_STATUS
        , lead (EFFDT, 1) over (partition by EMPLID order by EFFDT) as NEXT_EFFDT
    from ps_job
    order by EMPLID, EFFDT
)
select EMPLID
    , trunc(sum((trunc(coalesce(NEXT_EFFDT, current_timestamp)) - trunc(EFFDT)) / 7)) as WEEKS_ACTIVE
from q
where HR_STATUS = 'A'
group by EMPLID;

The coalesce function will grab the system date in the event it cannot find a matching I record (employee is current). You could substitute the end of the year if that's your spec.

Note that I'm not doing any rigorous testing to see that your entries are ordered A/I/A/I etc., so you might want to add checks of that nature if you know your data requires it.

Feel free to play with this at SQL Fiddle.