Report query average timespent on request

Report query average timespent on request

Hi


I am using SQL 2008 R2 as our SCP DB. I have a SQL query working in SQL but not in SCP. Here is the SQL query.

select GroupName , Count ( WorkID ) "No. of Tickets" , ( select CONVERT ( varchar , DATEADD ( ms , AVG ( ElapsedTime ), 0 ), 108 )) "Average Elapsed Time"

From (

SELECT queue . QUEUENAME "GroupName" , wo . WORKORDERID "WorkID" , wo . TIMESPENTONREQ "ElapsedTime" , wo . COMPLETEDTIME "Completed Time"

FROM WorkOrder wo

LEFT JOIN WorkOrderStates wos ON wo . WORKORDERID = wos . WORKORDERID

LEFT JOIN CategoryDefinition cd ON wos . CATEGORYID = cd . CATEGORYID

LEFT JOIN WorkOrder_Queue wo_queue ON wo . WORKORDERID = wo_queue . WORKORDERID

LEFT JOIN QueueDefinition queue ON wo_queue . QUEUEID = queue . QUEUEID

WHERE ((( cd . CATEGORYNAME = N'Quote' COLLATE Latin1_General_CI_AS )

AND ((( wo . COMPLETEDTIME >= 1438560000000 ) AND (( wo . COMPLETEDTIME != 0 )

AND ( wo . COMPLETEDTIME IS NOT NULL))) AND (( wo . COMPLETEDTIME <= 1439164799000 )

AND ((( wo . COMPLETEDTIME != 0 ) AND ( wo . COMPLETEDTIME IS NOT NULL))

AND ( wo . COMPLETEDTIME != - 1 ))))) AND ( wo . DEPARTMENTID = 1 ))  

AND wo . ISPARENT = '1' ) t

Group by GroupName

order by GroupName


How can I get this to function within SCP. I only need last weeks information.


Thanks in advance.

              New to ADManager Plus?

                New to ADSelfService Plus?