0
    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

2 Answers 2

0

One solution would be to add the membership as a column in orders:

Orders
Date       | ClientID | Rev | Membership
2023-02-02 |      1   | 10  |  1
2024-02-01 |      1   | 20  |  2
1
  • why? you join the membership table and can check if the order date is between start and end date, so no need to save the id
    – nbk
    May 15 at 15:34
0

As mentioned by @nbk, there is no need to denormalise memberships to achieve what you want. If you do, you risk having conflicting information in memberships and orders. I changed some of your attribute names:

select t.team, sum(o.rev)
from memberships m
join teams t
  using (team_id)
join agents a
  using (agent_id)
join orders o
  on o.order_date between m.start_date and m.end_date
group by t.team;

I used Postgres in the following Fiddle, but it shouldn't be too difficult to adapt it to the DBMS that you use

If your DBMS supports temporal business time, you may consider using that for this type of scenario. One advantage is that you can easily prevent overlapping intervals.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.