Query AVG help
-
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_
-
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_
Got it...Thanks Srinivas! 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
Regards, Hulicat