question

mikejimmy632 avatar image
mikejimmy632 asked

Aggregation of time

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
aggregation
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.

1 Answer

· Write an Answer
Waqar_lionheart avatar image
Waqar_lionheart answered
you could extract number of minutes datediff function??? (later convert them to hours) or just get hours and use an aggregate function and group by that could solve your problem i think
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.

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.