Fetching employee using BIP query if employee have Job changes and Termination

Summary:

Fetch the details of employee who have job changes and termination details of employee

Content (required):

Hi,

I am working on BIP query to fetch the details of employee only when employee have either job changed or termination based on below scenarios.

1)If employee has only job change then there should be only one row with old and New Job details and termination related fields should be blank

2)If employee has both job change and termination then the report should fetch two rows one for job change and one for termination.

3)If employee has only termination but no job change then it should fetch only one row with termination details column and job related column should be blank.