Retrieve Department Hierarchy per User Assignment (with Effective Date)

Summary:

Hi everyone,

I'm wondering if anyone has a query that returns the organizational department structure for users based on their assignment, while properly considering the effective start date.

What I need is to retrieve the structure in the same format that appears in reports extracted from the subject area, like the example shown below:

Some key points:

• The query must take into account the effective start date of the assignment. For example, if an organizational restructure happened, a person might belong to structure X before a certain date and to structure Y after.
• It also needs to handle department name changes