Complex Date Filtering w/ Two Start and End Dates

1

Context

I have two tables, one contains projects with a start and end date as well as rows with numbers of people in certain positions that need to be assigned to the project and the other contains the assignments with a person reference and start and end date of said assignment as well. Essentially, a project has a start and end date and there can be multiple assignments to each position needed between those dates (many to one relationship).

Problem

In the project table, I need to iterate through all of the assignments for a row to determine whether or not I am missing assignments to a certain position within the project for any given whole week.

Constraints

Theoretically, I should have no issues adding columns or tables to manage all of this. Also, this is within a paid account and so we do have the luxury of packs if there are any packs that can manage any of this.

Example

In this example, I should be missing a human during the first week and last week of the project. I need the result to be an array of Monday dates.

2

Dear @Tanner_Hess_Webber,

Would you mind to update your sharing settings of your sample to make it available to access without the need to “request for access”?

Thank you :handshake:

3

@Jean_Pierre_Traets - Updated! Sorry about that!

4

Dear @Tanner_Hess_Webber,

I am quite sure that this post contains the elements to identify the missing assignment days, but to be honest I don’t have the time to play around with it.

Hopeful other members will be able to give you a helping hand :handshake:

5

@Jean_Pierre_Traets - Thanks for the help and for pointing me that direction!

My use-case is actually to look at this in a week view and it didn’t occur to me to do it in said view, which makes it much more straightforward than to do it in the projects table itself because I needed to be accessing current values from multiple scopes!

Thanks!