Queries to show merged requests (PGSQL & MSSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL:
SELECT wo.workorderid "Request ID",
wo.TITLE "Subject",
LONGTODATE(wo.CREATEDTIME) "Created Time",
LONGTODATE(wo.DUEBYTIME) "DueBy Time" ,
array_to_string(array_agg(Child_woid), ',') "Merged Request" ,
MAX(aaa.first_name) "merged by" from conversation
LEFT JOIN workorder wo on conversation.Workorderid=wo.Workorderid
left join WorkOrderHistory woh on wo.workorderid=woh.workorderid
left join aaauser aaa on woh.operationownerid=aaa.user_id
where
Child_woid is not NULL and
woh.operation='MERGEWITH'
GROUP BY wo.Workorderid
MSSQL:
SELECT conversation.Workorderid "Parent Request", wo.title "Subject",
(SELECT STUFF(
(SELECT cast(Child_woid AS varchar) + char(10)
FROM conversation co
WHERE conversation.Workorderid=co.Workorderid
FOR XML PATH ('')), 1, 0, '')) "Merged Request" FROM conversation LEFT JOIN workorder wo on conversation.Workorderid=wo.Workorderid
WHERE Child_woid IS NOT NULL
GROUP BY conversation.Workorderid, wo.title
New to ADSelfService Plus?
Related Articles
Query to show shared requests (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Shared to Tech: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show parent and child requests (Linked Request) and its details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute this query. select woparent.WORKORDERID "Parent request ID", aaauparentreq.first_name "Parent Requester", woparent.title "Parent Subject", ...Query report to show Open requests without open tasks
PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...