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.
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.