SQL to find the number of weeks when an employee was active between two dates

Question

I have a table with a list of dates when an employee became active / inactive, and I want to count the weeks when an employee was active for a certain date range.

Thus, the table (ps_job) will have the following values:

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 request must indicate the number of weeks during which this emplid was active from July 1 to December 11 to December 31.

Desired result set:

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

I got number 35 by adding the results from SQL:

 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 that I cannot figure out how to create a single request statement that will go through all the lines for each employee for a certain date range and just return each emplid and the number of weeks that they have been active. I would prefer to use basic SQL instead of PL / SQL so that I can pass it to the PeopleSoft query that can be run by the user, but I am ready to run it for the user using Oracle SQL Developer, if necessary.

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

Answer 1

Here I use lead in the subquery to get the following 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 capture the system date if it cannot find the corresponding record I (the employee is current). You can replace the end of the year if this is your specification.

Please note that I am not doing rigorous testing to see that your records are being ordered A / I / A / I, etc., so you might want to add checks of this nature if you know that your data requires it.

Feel free to play with this in SQL Fiddle .

Answer 2

If the client just wants a rough estimate, I would start with the number of days for each stay, divided by 7 and rounded.

The trick is to build an active date with the corresponding Inactive date, and the best way I can do this is to select the Active and Inactive dates separately, rank them by date and combine them together with EmplID and rank. The analytic function ROW_NUMBER() is the best way to evaluate in this situation:

 WITH EmpActive AS ( SELECT EmplID, EffDt, ROW_NUMBER() OVER (PARTITION BY EmplID ORDER BY EffDt NULLS LAST) DtRank FROM ps_job WHERE HR_Status = 'A' ), EmpInactive AS ( SELECT EmplID, EffDt, ROW_NUMBER() OVER (PARTITION BY EmplID ORDER BY EffDt NULLS LAST) DtRank FROM ps_job WHERE HR_Status = 'I' ) SELECT EmpActive.EmplID, EmpActive.EffDt AS ActiveDate, EmpInactive.EffDt AS InactiveDate, ROUND((NVL(EmpInactive.EffDt, TRUNC(SYSDATE)) - EmpActive.EffDt) / 7) AS WeeksActive FROM EmpActive LEFT JOIN EmpInactive ON EmpActive.EmplID = EmpInactive.EmplID AND EmpActive.DtRank = EmpInactive.DtRank 

The third concert for EmplID = 1000 has an active date, but without an inactivity date, so NULLS LAST in the order of ROW_NUMBER and the left join is between two subqueries.

I used the "days / 7" math here; You can replace what you need when you hear a response from the client. Please note: if there is no corresponding inactive date, the request uses the current date.

Here's the SQLFiddle of this here .

Answer 3

The following should work on what you are trying to do. I had to hardcode the end date in an NVL statement

 SELECT emplid, hr_status, ROUND(SUM(end_date - start_date)/7) num_weeks FROM (SELECT emplid, hr_status, effdt start_date, NVL(LEAD(effdt) OVER (PARTITION BY emplid ORDER BY effdt), TO_DATE('12312012','MMDDYYYY')) end_date FROM ps_job ) WHERE hr_status = 'A' GROUP BY emplid, hr_status ORDER BY emplid 

An internal query will pull information about the status of the employee and character from the table and use the effdt column as the start date and use the LEAD analytic function to get the next value of the distribution date from the table, which indicates the beginning of the next status and this will be the end_date of the current row. If the LEAD function returns NULL, we assign it the end date (12/31/2012) that you wanted. it then limits the result set to records with active HR status and calculates the weeks.