We have a number of field engineers who attend a number of calls every day.
I'm trying to produce a report to show their average first time to site and their average last time off site.
So for example, Engineer A will visit 3 calls on Day 1 - on-site at 10.00, 12.00 & 14.00
on Day 2, he visits another 3 calls - on-site at 9.00, 11.00 & 13.00
The only two calls I'm interested in are his 10.00 on Day 1 & 9.00 on Day 2, for which I want to display an avergae on-site time of 9.30.
Within my report I can display his earliest on-site time (using MIN function) by grouping by engineer and then date - giving me 10.00 for day 1 and then 9.00 for day 2.
However, what I can't then do is avergae out those times at engineer level as SQL RS does not allow me to aggregate on an aggregated value.
I need to do a similar thing with the last off-site time (MAX instead of MIN)
Attached is a export of the table with Engineer A
What's the best of way of achieving this?