Query to find the number of follow up sent by technician
Use Case
The query retrieves the count of number of conversation matching the keywords
Sample Table
DB: MSSQL & Postgres
Query
select wo.WORKORDERID "Request ID",
longtodate(wo.CREATEDTIME) "Created Time",
qd.QUEUENAME "Group",
wo.TITLE "Subject",
pd.PRIORITYNAME "Priority",
count(notificationtodesc.notificationid) "No.of follow up" from notificationtodesc
left join notification no1 on notificationtodesc.notificationid=no1.notificationid
left join notify_workorder nwo1 on no1.notificationid=nwo1.notificationid
left join workorder wo on nwo1.workorderid=wo.workorderid
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
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 notificationtodesc.description like '%follow%' or no1.notificationtitle like '%follow%' and wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
group by wo.WORKORDERID,wo.CREATEDTIME,qd.QUEUENAME,wo.TITLE,pd.PRIORITYNAME
Note :
Description keyword is highlighted in yellow and subject in green.
Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
- This year - <from_thisyear> - <to_thisyear>
- Last year - <from_lastyear> - <to_lastyear>
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 ...Technician
This report is used to find the Technician complete details. SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...Request violated by technician
This report used to find the technician who violated the request. If the request/incident already has a violation and is reassigned to another technician the new technician assumes the violation instead of the technician that the violation occurred. ...Technician change from history
This report is used to find who changed the Technician and how many technician handled the request. To make any changes to a query, refer to the KB article below. ...Query to show Approver details, sent, acted on time along with time taken to approve. ( 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", ...