Hello, can anyone help me with counting the elapsed time, given a table of datetime ranges? Note that some datetime ranges may overlap. Can't be done in the load script because this has to be computed ad hoc, based on user filters.
| Log ID |
Start Date Time |
End Date Time |
Affected ID |
| 1 |
1/01/2022 7:15 |
1/01/2022 10:15 |
A |
| 1 |
1/01/2022 7:15 |
1/01/2022 10:15 |
B |
| 1 |
1/01/2022 7:15 |
1/01/2022 10:15 |
C |
| 2 |
1/01/2022 8:00 |
1/01/2022 11:15 |
A |
| 2 |
1/01/2022 8:00 |
1/01/2022 11:15 |
D |
| 3 |
1/01/2022 15:00 |
1/01/2022 16:00 |
A |
The output i am looking to get is, the Total mins elapsed per Affected ID:
| Affected ID |
Log ID |
Start Date Time |
End Date Time |
Sub-total mins affected |
Total mins affected |
| A |
1 |
1/01/2022 7:15 |
1/01/2022 10:15 |
240 mins |
300 mins |
| 2 |
1/01/2022 8:00 |
1/01/2022 11:15 |
| 3 |
1/01/2022 15:00 |
1/01/2022 16:00 |
60 mins |
Thank you.
