Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

AVG in query 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_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.

On May 30, 4:28 am, Hulicat <dennis_A_wh@yahoo.com> wrote:

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

-----------------------------------------------Reply-----------------------------------------------

On May 30, 1:30 am, M A Srinivas <masri@gmail.com> wrote:

That was it; days is fine.

Thanks it worked and I learned how do do that as well

It's greatly appreciated........TY

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc