Need to create a report on pending tickets where last reply by requester
Hello,
I need to produce a report of all pending tickets where the last conversation was by the requester along with the date and time they replied (i.e. technician has not yet replied to the requester). I have an existing tabular report filtered where 'Last reply' is 'REQ_REPLY', but it doesn't list or have an option to show the date/time of the requesters reply. It does show Last Update Time, but that could relate to anything. Running on MSSQL database. SQL for the existing report below
- SELECT "qd"."QUEUENAME" AS "Group", "wo"."WORKORDERID" AS "Request ID", "serdef"."NAME" AS "Service Category", "aau"."FIRST_NAME" AS "Requester", "wo"."TITLE" AS "Subject", "qd"."QUEUENAME" AS "Group", "ti"."FIRST_NAME" AS "Technician", "std"."STATUSNAME" AS "Request Status", "wo"."CREATEDTIME" AS "Created Time", "wo"."DUEBYTIME" AS "DueBy Time", "wos"."ISOVERDUE" AS "Overdue Status", "wos"."LAST_TECH_UPDATE" AS "Last Update Time" FROM "WorkOrder" "wo" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID" LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID" LEFT JOIN "ServiceDefinition" "serdef" ON "wo"."SERVICEID"="serdef"."SERVICEID" LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID" LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID" LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID" LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID" WHERE ( ( ( ( "serdef"."NAME" = N'Incident' ) OR ( "serdef"."NAME" = N'Service Request' ) ) AND ( ( ( ( "std"."STATUSNAME" = N'Assigned' ) OR ( "std"."STATUSNAME" = N'In-Progress' ) ) OR ( "std"."STATUSNAME" = N'Onhold' ) ) OR ( "std"."STATUSNAME" = N'Open' ) ) ) AND ( "wos"."NOTIFICATIONSTATUS" = N'REQ_REPLY' ) ) AND wo.ISPARENT='1' ORDER BY 1, 12
New to ADSelfService Plus?