Query to find replies sent to a requester excluding the system notifications

Query to find replies sent to a requester excluding the system notifications


DB: MSSQL

Query

select wo.workorderid as "Ticket ID",
convert(date, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')) AS "Creatd Date",
format (convert(time, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')), 'hh') + ':' +
format (convert(time, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')), 'mm') + ':' +
format (convert(time, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')), 'mm') AS "Create Time",
n.NOTIFICATIONID,
convert(date, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')) AS "Reply Date",
format (convert(time, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')), 'hh') + ':' +
format (convert(time, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')), 'mm') + ':' +
format (convert(time, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')), 'mm') AS "Reply Time",
n.NOTIFICATIONTITLE as "title",
aau.FIRST_NAME as "sender",
au.FIRST_NAME as "Recipient",
nr.RECIPIENT_EMAIL "Email",
au.USER_ID from notify_workorder nw
left join Notification n on n.NOTIFICATIONID = nw.NOTIFICATIONID
LEFT JOIN notification_recipients nr ON nr.NOTIFICATIONID=n.NOTIFICATIONID
left join WorkOrder wo on wo.WORKORDERID = nw.WORKORDERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN accountsitemapping asm on asm.siteid=sitedef.siteid
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
LEFT JOIN WorkOrder_Fields wof ON wof.workorderid=wo.workorderid
LEFT JOIN AaaUser aau ON n.SENDERID=aau.USER_ID
LEFT JOIN AaaUser au ON n.RECIPIENTID=au.USER_ID
where nr.RECIPIENT_EMAIL='req4@abc.com'
and Notificationtype = 'REQREPLY'
and n.SENDERID != '1'
and (wo.createdtime/1000between datediff(s,'1970-01-01 00:00','2022-07-01 00:00') and datediff(s,'1970-01-01 00:00','2022-12-31 23:59')) 

Notes 
  1. You can toggle n.SENDERID != '1' value to 1 or 0. 1=response, 0=automated notification. You can remove and n.SENDERID != '1' to get both the data at once. 
  2. You can replace the dates to your own time frame. 




      New to ADManager Plus?

        New to ADSelfService Plus?

        Start your free trial

          Resources

              • Related Articles

              • Query to find out who created Accounts

                Execute the below queries under  Reports->New  Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down the Org_IDs of the above query from the result ...

              • Query to list request IDs with Requester and Technician Employee ID

                Sample Table  Database : MSSQL Query SELECT "wo"."WORKORDERID" AS "Request ID", ti.FIRST_NAME AS "Technician", td.employeeid "Tech Employee ID", ti1.FIRST_NAME AS "Requester", td1.employeeid "Req Employee ID"  FROM "WorkOrder" "wo" LEFT JOIN ...

              • Status Change on new reply.

                The status do not automatically switch from "OnHold" to "Open" when a response from the Requester arrives. Enable below configuration to automate the same. Go to Admin >> Self Service Portal >> Enable the below option   Also, it does not switch from ...

              • Query to show Approver details, sent, acted on time along with time taken to approve.

                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", LONGTODATE(MAX(OPERATIONTIME)) ...

              • Query to find requests deleted by technician

                This report is used to find the deleted request in the application. We can get the data from the Trash and system log viewer.   To make any changes to a query, refer to the KB article below. SELECT err.message "System log message", err.errormodule ...

                Related Products