Databases
StackOverflow2013
Postsdbo
POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
    primarykey
    data
    text
    <p>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.</p> <p>So the table (ps_job) would have values like this:</p> <pre><code>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 </code></pre> <p>The query would need to show me the number of weeks that this emplid was active from 01-Jul-11 to 31-Dec-12.</p> <p>The desired result set would be:</p> <pre><code>EMPLID WEEKS_ACTIVE ------ ------------ 1000 35 </code></pre> <p>I got the number 35 by adding the results from the SQLs below:</p> <pre><code>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; </code></pre> <p></p> <p>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.</p> <p>Database: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USypercubeᵀᴹ
    1. USBarrettNashville
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you explain better your needs? You can have multiple entries in your table right? I mean, every week you store if an employee is activer or not? Or every month??
      singulars
      1. POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
      1. USMadthew
    2. COSo the table is updated whenever an employee is hired or terminated. In this case the employee was hired 01-Jul-11 so their HR_STATUS was A(ctive). Then they were terminated 01-Sep-11 so their HR_STATUS was I(nactive). Then they were rehired 01-Jan-12, terminated again 01-Mar-12, and then rehired 01-Sep-12 and are still active to this day. This is an extreme example but shows how an employee bouncing between active and inactive makes the query more difficult.
      singulars
      1. POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
      1. USBarrettNashville
    3. COIn your example you're trying to push the dates to Sundays. Say an employee is hired on Friday of this week (5/10/2013) and terminates Monday of next week (5/13/2013). Their "number of weeks" could be 0 (3 days rounded = 0 weeks), 1 (3 days = part of a week), or 2 (active within two different weeks). Which would you want to report?
      singulars
      1. POSQL to Find Number of Weeks an Employee Was Active Between Two Dates
      1. USEd Gibbs
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload