I need to keep track of total benchmark of a participant’s jobs that have a gap of less than 30 days between them. For example (see below), I need to add just the # of days of the 1st & 2nd job together and NOT include the 3rd job's # of days because there is a gap of 51 days between 2nd & 3rd job.
Master table is Participants and Child table is Employments
Name (ref. proxy) Employer Start End Duration
John Smith McDonalds 1/6/16 2/10/16 35 days
Then there is a 7 day gap between jobs
John Smith Walmart 2/17/16 5/3/16 44 days
Then there is a 51 day gap between jobs
John Smith Starbucks 7/2/16 8/3/16 32 days
Ideally, I want something that says that this person reached the 60 day benchmark because they have been “employed” (including a <30 day grace period) for 79 days. The next benchmark goal is 90 days.
Technically, the 3rd job has a separate benchmark of 30 days but I only need to display the highest level benchmark this person achieved which was 60 days. Any ideas?