Kindly help me with SQL Query to get Hire Date of an employee

Summary:

Kindly help me with SQL Query to get Hire Date of an employee
If an employee has been only globally transferred, query should return enterprise hire date
If an employee is rehire case, then rehire date should be returned
Thanks in advance

Content (please ensure you mask any confidential information):

Version (include the version you are using, if applicable):

Code Snippet (add any code snippets that support your topic, if applicable):