Hello,
I have a bunch of events stored on a DB, all of which contain a start time and end time in two different columns. The events have different types, which are also stored in a column. For a given time range, I would like to get how many time of events type A are overlaping with events type B and how many time events of type A are not overlapping with any other event type.
Here is a simplified example ( in my real data, I have thousands of rows and I need to be accurate to the seconds).
| Events | Type | Start time | End time |
| 1 | B | 02:00:00 | 04:00:00 |
| 2 | B | 04:00:00 | 05:00:00 |
| 3 | A | 04:30:00 | 05:30:00 |
| 4 | B | 05:30:00 | 07:30:00 |
| 5 | A | 07:25:00 | 07:55:00 |
| 6 | B | 07:50:00 | 08:50:00 |
| 7 | B | 08:50:00 | 09:00:00 |
| 8 | B | 09:00:00 | 10:00:00 |
In this I would like to get the Overlapping A on B from 02:00:00 to 10:00:00 and A non overlapping with any other event.
I would get : 40 minutes overlapping A on B and 50 minutes non overlapping
Any clue how I could get that done? I have tried manipulating the data with multi-row formula, but it gets really tricky and I don't believe I will be able to get to a solution efficienctly.
