Query to report number of days an employee has worked in each job code.

If you are a PeopleSoft analyst sometimes business may request you for a report that shows number of days an employee has worked in each department or job code during his service in the organization. You can achieve this by creating simple PSQuery or SQL. In this doodle I will walk you through an example SQL which you can use for your similar business requirement.

In my below example query, we will report how many days an employee has worked in each job code during his service in the organization.

SELECT 
INL.JOBCODE 
SUM(TO_DATE(INL.EFFDT,'DD-MON-YYYY') - TO_DATE(INL.PREV_EFFDT,'DD-MON-YYYY')) As 'Number of Days'
FROM 
( SELECT J.EMPLID,
J.EFFDT,LAG(J.EFFDT,1) OVER (ORDER BY EFFDT) AS PREV_EFFDT, 
J.JOBCODE,
LAG(J.JOBCODE,1) OVER (ORDER BY EFFDT) AS PREV_JOBCODE 
FROM PS_JOB J 
WHERE J.EMPLID =
ORDER BY J.EFFDT DESC ) INL 
GROUP BY INL.JOBCODE;

In the above SQL, we have achieved the requirement by building an inline query using the Oracle Lag function. Oracle lag function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table.

This inline query returns a result which has each adjacent row's job code and effective date. (i.e) Table that has the employees current rows job code and previous rows job code in a row. This result of  the inline query is group to achieve the number of days an employee worked in each job code.

You can use this query to replace with department or position number to generate reports.