Hello everyone,
I have a specific case to solve and I need your help after a few hours of trying hard unsuccessfully.
Here is the context:
I have a Timesheet table which is structured as follows :
Each person puts the number of hours the days they are working OR on leave
| ID | Lastname | Date | NbHours |
| 1 | Person A | 25/08/2022 | 8 |
| 1 | Person A | 26/08/2022 | 8 |
| 1 | Person A | 29/08/2022 | 8 |
| 2 | Person B | 22/08/2022 | 8 |
| 2 | Person B | 29/08/2022 | 8 |
| 3 | Person C | 26/08/2022 | 8 |
| 3 | Person C | 27/08/2022 | 8 |
| 3 | Person C | 28/08/2022 | 8 |
| 3 | Person C | 02/09/2022 | 8 |
And I have a "Leave Table" with the leaves for each person :
| ID | Lastname | Start Date | End Date |
| 1 | Person A | 22/08/2022 | 26/08/2022 |
| 2 | Person B | 10/08/2022 | 14/08/2022 |
| 2 | Person B | 20/08/2022 | 01/09/2022 |
| 3 | Person C | 02/09/2022 | 05/09/2022 |
These tables are obviously simplified ones but this is the idea
The two tables are linked through the ID with a *-* relationship (impossible to do another way)
What I need is to list the people that have worked more than 7 days in a row, and show how many days in a row they have been working (if there are multiple periods with more than 7 days for a single person, show the max)
Thing to notice : the people put hours in the timesheet also for their leaves so we need to use the "Leave Table" to not count the dates between Start and End columns
In this example, no one works more than 7 days in a row of course but we should have :
| ID | Lastname | Max days in a row |
| 1 | Person A | 1 |
| 2 | Person B | 0 |
| 3 | Person C | 3 |
I hope this is clear enough for you to understand and I hope someone will be able to help me.
Thank you in advance !
