Count by inactive relationships

[kasife]

Hi guys,

I'm having trouble taking a measurement. In the modeling below, I highlighted only the relationships that are important to facilitate understanding.

My projects table has the name of the project, the project start date and the end date.

My broker table has the column ID, project, registration date. And a broker can be in one or more projects

The relationships between them are inactive as you can see.

I would like to obtain the following result:

In the selected month I want to do a count of brokers, but this count needs to consider the end and start date of the project and whether the broker was registered after or during the project period.

[lbendlin · Accepted solution]

Inactive relationships are not allowed to introduce ambiguity/circular references when they are activated. The regular usage pattern for inactive relationships is for them to live next to an active relationship between the same tables.