Forum Discussion
Overlapping Time Data
Hi,
1) in Job Correction - add index to fix table in memory; sort by id and duration (ascending); add another index to fix in memory again, remove duplicate
2) corrected in Prep Corrected query
Please check in attached file
Hi SergeiBaklan,
Thank you so much for this information. This gets me pretty close to what I need. However, I have a few issues with it.
1) On the "Jobs Corrected" query, during the remove duplicates step it is removing the record with the lower duration when it should be removing the record with the higher duration (duration - downtime). These need to be ranked somehow so the correct record can be removed. I don't have Excel Online for that command.
2) The "Prep Corrected" query is counting the entire value of the prep time. It should only be counting the time where a job is NOT running. For example, below it should only count the prep time from 12:09 - 12:49 = 40 minutes not 49 minutes. I think I can use the same logic that you applied to the jobs here where you subtracted the overlap with the downtime from their durations.
| Job | 9/28/2023 10:22 | 9/28/2023 12:09 |
| Prep | 9/28/2023 12:00 | 9/28/2023 12:49 |
Hi,
1) in Job Correction - add index to fix table in memory; sort by id and duration (ascending); add another index to fix in memory again, remove duplicate
2) corrected in Prep Corrected query
Please check in attached file