x

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

more ▼

asked May 24 at 09:54 AM in Default

avatar image

mikejimmy632
1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Jul 13 at 09:02 AM

avatar image

Waqar_lionheart
101 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x4

asked: May 24 at 09:54 AM

Seen: 41 times

Last Updated: Jul 13 at 09:02 AM

Copyright 2017 Redgate Software. Privacy Policy