We help IT Professionals succeed at work.

SQL Report for average min & max

vistasupport
vistasupport used Ask the Experts™
on
Hi guys

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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't see the attached export, so this is pretty rough.


select avg(starttime)
from
 (select min(onsitetime) as starttime
  from tablename
  where engineerid = x)

Author

Commented:
i have attached the file again
first-time-onsite-offsite.xlsx
can you post the table structure and sample data?  That spreadsheet isn't very helpful in this case.

Author

Commented:
Table name is Asgnmnt and we are selecting

Assignee (for Engineers name)
DateOnsite (When the engineer updated the call with onsite date details)
TimeOnsite (When the engineer updated the call with onsite time)
TimeResolve (When the engineer updated the call with resolve time)

Engineers must fill these in on there PDA Each time they attend a site and leave (for resolve)

So the table is then grouped by Assignee and DateOnsite

On the Dateonsite level I select [TimeOnsite] in the next column and [timeResolve] in the column next to that,

Currently i am adding a expression to timeonsite and timeresolve =min(Fields!TimeOnsite.Value) and =max(Fields!TimeResolv.Value) which then leaves me the engineers first timeonsite and last time offsite figure in the dateonsite group level

I then add the same [TimeOnsite] and [TimeResolve] to the detail row so i can see all the times the engineer got to his calls)

But I cant seem to find a way in my report to add the engineers AVG to the assignee group

I am also using a date range parameter to pull the records that the user wants to see from dateonsite.
Are TimeOnsite and TimeResolve Datetime?

Author

Commented:
yes

Author

Commented:
Sorry thinking about it they are a string

Our helpdesk software puts the times as strings and when it reads it back it converts it in the software to datetime
Are they just the time portion, or the date as well?

Author

Commented:
both just time 00:00:00
See what this produces and if that's what you need.
select tt.assignee, tt.dateonsite, tt.timeonsite, tt.timeresolve,
(select convert(varchar, cast(avg(cast( cast(t2.mintimeonsite as datetime) as float )) as datetime), 108)
 from 
  (   select dateonsite, min(timeonsite) as mintimeonsite
   from tablename
   where assignee = tt.assignee
   and dateonsite between '04/01/2012' and '04/02/2012'
   group by dateonsite) as t2 ) as avgstarttime
from tablename tt
where tt.dateonsite between '04/01/2012' and '04/02/2012'
order by tt.assignee, tt.dateonsite, tt.timeonsite

Open in new window

Author

Commented:
i am getting a result now but not sure its the right one will look at the figures tomorrow and if correct make this as answerd thanks for the help

Author

Commented:
just check and 100% ok.  Thank you very much.

Author

Commented:
i am only getting the avg starttime what do i need to add to get max resolvetime
This will get you the average end time of the last site visit.

select tt.assignee, tt.dateonsite, tt.timeonsite, tt.timeresolve,
(select convert(varchar, cast(avg(cast( cast(t2.mintimeonsite as datetime) as float )) as datetime), 108)
 from 
  (select dateonsite, min(timeonsite) as mintimeonsite
   from tablename
   where assignee = tt.assignee
   and dateonsite between '04/01/2012' and '04/02/2012'
   group by dateonsite) as t2) as avgstarttime,
   
(select convert(varchar, cast(avg(cast( cast(t2.maxresolvetime as datetime) as float )) as datetime), 108)
 from 
  (select dateonsite, max(timeresolve) as maxresolvetime
   from tablename
   where assignee = tt.assignee
   and dateonsite between '04/01/2012' and '04/02/2012'
   group by dateonsite) as t2) as maxresolvetime   
   
from tablename tt
where tt.dateonsite between '04/01/2012' and '04/02/2012'
order by tt.assignee, tt.dateonsite, tt.timeonsite

Open in new window

Author

Commented:
Very good advise