Suppose I have the following tables
Hosts:
| Host Id | Host |
| 1001 | Abigail Morris |
| 1002 | Betty James |
| 1003 | Karla Carraso |
| 1004 | Lynn Williams |
Players:
| Player Id | Player |
| 2001 | Aaron Chambliss |
| 2002 | Michael Stewart |
| 2003 | Steve Jazz |
| 2004 | Wallace Stanley |
Host-Player History:
| Host Id | Player Id | Start Date | End Date |
| 1001 | 2001 | 1/1/2023 | 12/31/2023 |
| 1001 | 2002 | 1/1/2023 | 4/30/2023 |
| 1002 | 2002 | 5/1/2023 | 12/31/2023 |
| 1002 | 2003 | 2/6/2023 | 6/14/2023 |
| 1003 | 2003 | 6/15/2023 | 12/31/2023 |
| 1003 | 2004 | 3/15/2023 | 8/12/2023 |
| 1004 | 2004 | 8/13/2023 | 12/31/2023 |
Player Activity:
| Player Id | Date | Revenue |
| 2001 | 5/6/2023 | $1000 |
| 2002 | 3/15/2023 | $300 |
| 2002 | 7/20/2023 | $1500 |
| 2003 | 4/11/2023 | $2000 |
| 2003 | 8/9/2023 | $5000 |
| 2004 | 6/20/2023 | $7500 |
| 2004 | 11/29/2023 | $350 |
I have relationships between the Hosts, Players, and Host-Player History tables based on their respective Ids. I also have a relationship between Host-Player History and Player Activity based on Player Ids. I want to use these relationships to determine how much revenue each host generated while players were hosted to them, ie, when the date played is between the begin and end date for each host-player row, so that I can generate this type of report:
| Host | Total Revenue |
| Abigail Morris | $1300 |
| Betty James | $3500 |
| Karla Carrasco | $12500 |
| Lynn Williams | $350 |
What type of calculations and filters would I need to accomplish this?
