Wednesday, October 12, 2016

SQL to Identify When Job Data Field was Changed

Often in PeopleSoft we are trying to determine when an event became effective.  This SQL below is to identify when an employee's Company changed, but could be easily modified to identify when a department, position, supervisor or any job data element changed from the previous row.


SELECT NEW.EMPLID
, NEW.EFFDT
, NEW.COMPANY
, OLD.COMPANY
, OLD.HR_STATUS 
FROM PS_JOB NEW, PS_JOB OLD 
WHERE NEW.EMPLID = OLD.EMPLID 
AND NEW.EMPL_RCD = OLD.EMPL_RCD
AND NEW.COMPANY <> OLD.COMPANY 
AND OLD.EFFDT = (SELECT MAX(O_ED.EFFDT) 
                 FROM PS_JOB O_ED 
                 WHERE O_ED.EMPLID = OLD.EMPLID 
                 AND O_ED.EMPL_RCD = OLD.EMPL_RCD
                 AND O_ED.EFFDT <= (NEW.EFFDT -1))
Please note that this SQL does not use effective sequence, The reason for this is that I am not concerned with what sequence the company changed, I am only concerned about identifying the date.

No comments:

Post a Comment