> > I have the following stored prodecure and I need to add the average
> > time of the "Total closed for range" was opened for.

> > (
> > @startdate datetime,
> > @enddate datetime
> > )
> > as

> > select count(*) as 'Total closed for range', Priority_type_name
> > 'Priority', location_name 'Cient', email 'Engineer'
> > from job_ticket j
> > inner join priority_type p on p.priority_type_id = j.priority_type_id
> > inner join tech t on t.client_id = j.assigned_tech_id
> > inner join location l on l.location_id = j.location_id
> > where (last_status_update_time between @startdate and @enddate) and
> > status_type_id ='3'
> > group by l.location_name, t.email, p.Priority_type_name

> > The where clause is specifying when the last update = 3 which is
> > closed.

> > I suspect I need a sub query for a field named report_date and my
> > where clause (last_status_update_time between @startdate and @enddate)
> > and status_type_id ='3'?

> > here is what the table looks like:

> > ASSET_ID int Checked
> > ASSIGNED_TECH_ID int Checked
> > BILLING_RATE_ID int Checked
> > BILLING_TERM_ID int Checked
> > CLIENT_ID int Checked
> > CLOSE_DATE datetime Checked
> > DELETED int Checked
> > DEPARTMENT_ID int Checked
> > DISCOUNT money Checked
> > DUE_DATE_OVERRIDE datetime Checked
> > DUE_HOURS_MANUAL int Checked
> > FIRST_RESPONSE_DATE datetime Checked
> > IS_HOT int Checked
> > JOB_COST money Checked
> > JOB_TICKET_ID int Unchecked
> > JOB_TIME int Checked
> > LABOR_TAX_RATE money Checked
> > LAST_REMINDER_DATE datetime Checked
> > LAST_STATUS_UPDATE_TIME datetime Checked
> > LAST_UPDATED datetime Checked
> > LOCATION_ID int Checked
> > LOGGED_BY_ID int Checked
> > MAIL_CC_ADDRESS varchar(255) Checked
> > MODEL_ID int Checked
> > PARENT_ID int Checked
> > PHONE varchar(40) Checked
> > PO_NUMBER varchar(75) Checked
> > PRIORITY_TYPE_ID int Checked
> > PROBLEM_TYPE_ID int Checked
> > QUESTION_TEXT varchar(4000) Checked
> > REPORT_DATE datetime Unchecked
> > ROOM varchar(80) Checked
> > SEND_CARBON_COPY int Checked
> > SEND_CLIENT_EMAIL int Checked
> > SEND_TECH_EMAIL int Checked
> > SHIPPING money Checked
> > SHOW_DUE_DATE_ON_CALENDAR int Checked
> > STATUS_RED_NOTIFICATION_DT datetime Checked
> > STATUS_TYPE_ID int Checked
> > STATUS_YELLOW_NOTIFICATION_DT datetime Checked
> > SUBJECT varchar(255) Checked
> > SUBSCRIBER_ID int Checked
> > TASK_ELEMENT_ID int Checked
> > TECH_GROUP_ID int Checked
> > TICKET_TIME_AS_OF_LAST_UPDATE int Checked
> > TRAVEL_COST money Checked
> > TRAVEL_RATE_ID int Checked
> > TRAVEL_TIME int Checked
> > UPDATED_FLAG int Checked
> > USE_DUE_DATE_OVERRIDE_INTEGER int Checked
> > WORK_END_DATE datetime Checked
> > WORK_START_DATE datetime Checked
> > CC_ADDRESS_FOR_CLIENT varchar(255) Checked
> > INITIALIZED_CUSTOM_FIELDS int Checked
> > IS_PRIVATE int Checked
> > IS_TAX_FREE int Checked
> > LAST_CLIENT_REMINDER_DATE datetime Checked
> > LAST_CLIENT_UPDATE datetime Checked
> > SEND_TO_CLIENT_CC_LIST int Checked
> > SERVICE_TIME_ENABLED int Checked
> > SURVEY_RESPONSE_ID int Checked
> > TASK_ELEMENT_COMPLETE int Checked
> > UPDATED_BY_TECH_FLAG int Checked
> > ESCALATION_LEVEL int Checked
> > Unchecked

> > Results currently look like:
> > Total closed for range....... Priority........ Cient.........
> > Engineer ........

> > 24 ............................... Sev1.............. CA .............
> > swilliams........................

> > Desired Results:
> > Total closed for range....... Priority........ Cient.........
> > Engineer ........ Avaerage time open

> > 24 ............................... Sev1.............. CA .............
> > swilliams........................ value

> > I can not wrap my head around this for the life of me @ all.....any
> > help or direction would be greatly appreciated.

> Is average time open is in days,minutes,seconds ?
> Is the report_date is when the ticket was opened ?
> Not clear from your explanation what you require .

> select count(*) as 'Total closed for range', Priority_type_name
> 'Priority', location_name 'Cient', email 'Engineer' ,
> AVG(datediff(d,report_date,last_status_update_time )) AS [Avaerage
> time open]
> from job_ticket j
> inner join priority_type p on p.priority_type_id = j.priority_type_id
> inner join tech t on t.client_id = j.assigned_tech_id
> inner join location l on l.location_id = j.location_id
> where (last_status_update_time between @startdate and @enddate) and
> status_type_id ='3'
> group by l.location_name, t.email, p.Priority_type_name- Hide quoted text -

>