SQL help needed to get the effective start date of employee's assignment status change

Summary

SQL help for determining effective start date

Content

Hi All,

Really hoping someone can help me out. I got really close on my query but I am missing something when it comes to future dated transactions.

I would like to pull in the Current assignment as of Today and be able to display when the employee was changed to that status

For example: On Sept 15th and employee is set to come back from leave, but right now, their assignment status is set to Unpaid Leave

I need to report when the employee started the unpaid leave.

The original query came from a different post and it was using the audit table PER_ALL_ASSIGNMENTS_M_ unfortunately I discovered that several of our employees are missing from that table so I have to use the PER_ALL_ASSIGNMENTS_M