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 ADSelfService Plus?

        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 ...
            • 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 status change, its time and comments.

              Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", ...
            • 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 ...