I have a table of events that have a start date, a duration (in days) and an end date (calculated off of start + end date).
I need to check them for overlap, such that a given event can see other events that overlap with it.
I have about 400 rows currently and what was once silky smooth is now 15sec to create a row.
My formula is essentially:
- Make sure
thisRow.durationare not blank (if they’re blank, we don’t want to bother with all the other calculations)
- Run this fun Min/Max calculation to check for overlaps
Any ideas for how to optimize this further?
My brain goes to some kind of coarse batching as an initial step. My events are never more than about a week, so maybe I could group them into monthly batches, and number the batches. Then you could start by saying “is thisRow in the same batch or an adjacent batch”? (Something like
AbsoluteValue(currentValue.batchNumber - thisRow.batchNumber) > 1).
Any other ideas?