Query to show last conversation and last notification in a request ( PGSQL )
Tested in Build PGSQL (14300)
SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
wo.TITLE "Subject",
ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
(select conversationdescription.description from conversationdescription left join conversation con1 on conversationdescription.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid) "Last requester response",
(select notificationtodesc.description from notificationtodesc left join notification no1 on notificationtodesc.notificationid=no1.notificationid left join notify_workorder nwo1 on no1.notificationid=nwo1.notificationid where no1.notificationid in (select max(notification.notificationid) from notification left join notify_workorder on notification.notificationid=notify_workorder.notificationid group by notify_workorder.workorderid) and nwo1.workorderid=wo.workorderid) "Last Tech reply" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 WHERE std.ispending='true'
AND wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-11-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-11-30 00:00:00') * 1000 AS BIGINT)
order by 1
New to ADSelfService Plus?
Related Articles
Query to show last conversation and last notification in a request ( MSSQL )
Tested in Build MSSQL (14306) Query SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", (select conversationdescription.description from ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query to show Last Status Changed Time and Time spent in previous status ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group", std.STATUSNAME "Request Status", ...Query to show last notes added in request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) select wo.workorderid "request id", max(wo.title) "subject", max(qd.queuename) "group", max(std.statusname) "request status", max(pd.priorityname) "priority", max(sdo.name) "site", max(wo.createdtime) ...Query to show last updated field (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Kindly go to Reports-New Query Report and execute the below query. PGSQL: SELECT wo.WORKORDERID AS "Request ID", max(wo.TITLE) AS "Subject", max(ti.FIRST_NAME) AS "Assigned Technician", ...