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