How to pick only one TaskID if other records with same ID starts and ends in between
Requirement: I have multiple tasks for a single RequestID and sometime same tasks can be repeated multiple times for the same RequestID with different Start DateTime and End DateTime.
What I want is to check the overlapping of tasks with same ID based on the Start/End Dates. For ex: TaskID 751 is being repeated 3 times for the same RequestID i.e. 2.
RequestID TaskID OpenDate ClosedDate
1 750 07/10/2019 9:20:22 07/12/2019 9:20:22
2 751 07/08/2019 15:40:22 07/15/2019 9:20:22
2 751 07/10/2019 12:24:22 07/12/2019 17:50:22
2 751 07/13/2019 16:24:22 07/15/2019 8:33:22
Now if I do this record by record i will have 3 different Lead Times for TaskID 751 which will impact the final Lead time when i'll do the aggr. Since the second and third iteration of TaskID 751 got opened and closed during the time period of the first instance of the task so the time difference of only first instance should be considered and calculated.
I hope i have made the requirement clear enough, if not then please let me know.
I need to get this done quickly to move forward in the development, so any suggestions will be appreciated.
You can search the Design Blog area for further items, but I thought these two might be in play here. If you came up with something else, please post and mark that as the solution using the Accept as Solution button.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.