Is it possible to get current and previous salaries on one report?

I'm new to peoplesoft and I'm currently building a report where I hope to have the current and prior salary for an employee over a period of time. As an example, if employee A got a salary increase in 2017, then I would expect to see the prior and current salary in two different columns. The report that I'm trying to modify is all current information. I've added the EE Job record which houses the salary data. The report already had this record, so now there are two; A and C. I want to use C as a look back to view the prior salary.

I thought I could do this by modifying the criteria, condition type (eff date <) and then expression two as a field (A.EFFDT). This didn't work though.

Am I totally off base (can peoplesoft do this?)

Solution

I think a complete solution accounting for all EFFDT and EFFSEQs would be difficult within PeopleSoft Query, but your idea can get close with a couple assumptions.

One would be that to get the "old salary" we always look to a JOB row at least a day before the "current" JOB row. That means I cannot see a salary change that happens on the same day with multiple EFFSEQs.
A second assumption is not dealing with multiple jobs, so same EMPL_RCD.

You would have a query as you describe, with the JOB table added twice, so JOB A and JOB C. In the criteria you would have the following:

A.EFFDT Eff Date <= Current Date (EffSeq = Last) -- if you only want current rows
AND A.EMPLID equal to B.EMPLID
AND A.EMPL_RCD equal to B.EMPL_RCD
AND Field B.EFFDT Eff Date < Field A.EFFDT EffSeq = Last

You can also add active employees only, or a specific EMPLID as needed, but don't want any other joins between the JOB tables.

The SQL would look something like this...

select a.emplid, a.empl_rcd, a.effdt, a.effseq
     , a.annual_rt as new_salary, c.effdt, c.effseq, c.annual_rt as old_salary
from ps_job a, ps_job c
where a.emplid = c.emplid
and a.empl_rcd = c.empl_rcd
and a.effdt = (select max(a1.effdt) from ps_job a1
               where a1.emplid = a.emplid
               and a1.empl_rcd = a.empl_rcd
               and a1.effdt <= sysdate)
and a.effseq = (select max(a2.effseq) from ps_job a2
                where a2.emplid = a.emplid
                and a2.empl_rcd = a.empl_rcd
                and a2.effdt = a.effdt)    
and c.effdt = (select max(c1.effdt) from ps_job c1
               where c1.emplid = c.emplid
               and c1.empl_rcd = c.empl_rcd
               and c1.effdt < a.effdt)
and c.effseq = (select max(c2.effseq) from ps_job c2
                where c2.emplid = c.emplid
                and c2.empl_rcd = c.empl_rcd
                and c2.effdt = c.effdt)
order by a.emplid, a.empl_rcd, a.effdt desc, c.effdt desc

This would give current row for each EE and the rate on the max job row that is at least the day previous. Remove the current row logic if you want all rows.