question

mikejimmy632 avatar image
mikejimmy632 asked

Overlap time aggregration in sql

Hello I have 2 tables with workers and supervisor data I need the output as any aggregation data of hours spent by supervisor with worker. There will be overlap of time but we need to consider only the time he spent not overlap as additional time spent. This needs be done for all the supervisors in the table dynamically. Worker Working hours with supervisor Supervisor | Worker | Start Time | End time ---------------------------------------------------------------------------------------------------------------- Z8956 | Mike | 10:33:40 AM | 4:34:23 PM Z8956 | Robin | 2:52:03 PM | 3:29:22 PM Z8956 | Riche | 3:26:55 PM | 4:34:23 PM D6635 | Sam | 12:38:46 PM | 1:00:27 PM D6635 | Jeff | 8:38:23 AM | 9:51:26 AM D6635 | Joe | 7:56:54 AM | 8:29:31 AM supervisor working hours Supervisor | Start Time | End time -------------------------------------------------------------------------------------------------------------------------- Z8956 | 10:00:00am | 5:00:00 PM D6635 | 7:00:00am | 2:00:00 PM Output should be as below: This should not consider the overlap time Supervisor | Total Hours Spent with worker | Total Hours supervisor is free ---------------------------------------------------------------------------------------- Z8956 | 180mins | 200mins D6635 | 200mins | 500mins Any help greatly appreciated
aggregates
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.