asked on

SQL Report for average min & max

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?

David Kroll

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)

vistasupport

ASKER

i have attached the file again
first-time-onsite-offsite.xlsx

David Kroll

can you post the table structure and sample data? That spreadsheet isn't very helpful in this case.

vistasupport

ASKER

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.

David Kroll

Are TimeOnsite and TimeResolve Datetime?

vistasupport

ASKER

yes

vistasupport

ASKER

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

David Kroll

Are they just the time portion, or the date as well?

vistasupport

ASKER

both just time 00:00:00

David Kroll

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

vistasupport

ASKER

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

vistasupport

ASKER

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

vistasupport

ASKER

i am only getting the avg starttime what do i need to add to get max resolvetime

ASKER CERTIFIED SOLUTION

David Kroll