How to calculate revenue by sales team if sales agents change teams?

Agents ID | Agent 1 | A 2 | B Teams ID | Team 1 | AA 2 | BB Memberships ID | AgentID | TeamID | Start Date | End Date 1 | 1 | 1 | 2023-01-01 | 2023-12-31 2 | 1 | 2 | 2024-01-01 | 2099-12-31 Clients ID | AgentID 1 | 1 Orders Date | ClientID | Rev 2023-02-02 | 1 | 10 2024-02-01 | 1 | 20 How to get the following table based on the above? Team | Rev AA | 10 BB | 20