SQL to pull Previous Line Manager

Summary

Sample SQL to pull Line Manager from the Previous Assignment Row

Content

We have created a data model that pulls most current assignment row where actions = Promotion and also pulls in the previous assignment details. Example: It  pulls in details for current Line Manager, Dept, Job, Job Family, Grade etc and prior assignment details (where PER_ALL_ASSIGNMENTS_M_Previous row"."EFFECTIVE_END_DATE" ="PER_ALL_ASSIGNMENTS_M_Current row"."EFFECTIVE_START_DATE"-1) for the same fields.

The challenge we face is with the previous Line Manager data.  If the previous assignment row had a manager change then there is an associated row in the PER_ASSIGNMENT_SUPERVISORS_F table and the line manager data pulled into the data model is correct.