Sum Overlapping Time/Remove Timeframe Gaps

Question

Hi all, is there a way to do this formula queries? I have a table of people who have child residence & employment records, with start & end dates for each, as well as a numeric field that summarizes each timeframe in months. I need to query for all child residence & employment records related to each person, find any potential overlapping timeframes between the 2 tables based on start & end dates, and sum the total # of months from the Duration (months) field from applicable records.

Essentially I need a final number in months of time each person provided to us across 2 tables, so any overlapping time would basically get deduped out. I also need it to be smart enough to recognize some of these timeframes are not contiguous and contain gaps, so we might not have any date for particular person from 2010-2015, but we do from 2005-10 and 15-18, so any gaps would need to be excluded from the final count.

donlarson · Answer

Do you need a total for all time or will you have to specify specific periods?

For a small number of records in the People table I think I have a solution. If there are thousands of People than a solution is in order.

harrisonsmith · Answer

There's ~150 people, so relatively few&nbsp;

harrisonsmith · Answer

a total for all time. I have a calc start &amp; end formula date fields that put a cap on the timeframe if the provided date ranges exceed the relevant timeframe. There are currently 100 claimants, 210 residence records, and 40 employment records, knowing that will increase in the future, but it shouldn't get into the 1000's.&nbsp;

markshnier__you · Answer

Would it be OK to bucket the analysis into say monthly buckets? Say you made 36 monthly buckets for the last 36 months? There may be a solution if we use that approach. It's brute force with 36 or 72 summary fields, but that might work.

harrisonsmith · Answer

Well my ultimate goal is I need to categorize each person into 6 buckets:

lived/worked within 2 mile radius for >= 10 years
lived/worked within 2 mile radius for >= 5 years
lived/worked within 3 mile radius for >= 10 years
lived/worked within 3 mile radius for >= 5 years
lived/worked within 4 mile radius for >= 10 years
lived/worked within 4 mile radius for >= 5 years

The proximity distance field is a numeric field that exists on each residence/employment record (anyone who is categorized into the 2 mile/10 year bucket will automatically fall into each subsequent bucket, etc.).

My assumption was that whatever solution can work for 1 bucket can be duplicated for the others, so I would ultimately probably need 3 numeric fields (1 for each distance threshold) that specifies the amount of months each person spent at that distance, to go along with 6 formula checkboxes (1 for each category above) that look at whether each distance duration number is >= 120 (for 10 years) or >= 60 (for 5 years).

Forum Discussion

Sum Overlapping Time/Remove Timeframe Gaps