Query to show parent and child requests (Linked Request) and its details. PGSQL & MSSQL

Query to show parent and child requests (Linked Request) and its details. PGSQL & MSSQL

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",
stdparent.statusname "Parent Request Status",
aaauparent.first_name "Parent Request Technician",
LONGTODATE(woparent.createdtime) "Parent Request Created Time",
woschild.WORKORDERID "Linked Request ID",
wochild.title "Linked Request Subject",
aaauchild.first_name "Linked Request Technician",
LONGTODATE(wochild.createdtime) "Linked Request Created Time",
stdchild.statusname "Linked Request Status",
LONGTODATE(wochild.duebytime) "Linked Request Due By Time" from WorkOrder as woparent 
left join workorderstates wosparent on woparent.workorderid=wosparent.workorderid 
left join WorkOrderStates woschild on woschild.LINKEDWORKORDERID=woparent.WORKORDERID 
left join workorder wochild on woschild.workorderid=wochild.workorderid
left join statusdefinition stdparent on stdparent.statusid=wosparent.statusid
left join statusdefinition stdchild on stdchild.statusid=woschild .statusid
left join aaauser aaauparent ON wosparent.ownerid=aaauparent.user_id
left join aaauser aaauchild ON woschild.ownerid=aaauchild.user_id
left join aaauser aaauparentreq ON woparent.requesterid=aaauparentreq.user_id
where woschild.LINKEDWORKORDERID!=0 AND woparent.CREATEDTIME >= <from_thisweek> AND woparent.CREATEDTIME <= <to_thisweek>
ORDER BY 1

      New to ADSelfService Plus?

        Resources

            • Related Articles

            • How to automatically create multiple child requests from a parent request with a configurable JSON file (V3)

              This is a sample Python script to automatically trigger the creation of multiple child requests from a parent request, based on the request template chosen. The request ID of the child requests will be added as a Note in the parent request. This is ...
            • Query to show request details along with technician's department

              PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician ...
            • Query for request attachment details

              Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account",  wo.WORKORDERID AS "Request ID",  wo.TITLE AS "Subject",  ti.FIRST_NAME AS "Technician",  sa.ATTACHMENTNAME "Attachment Name", sa.ATTACHMENTPATH "Attachment Path" FROM ...
            • Query to show Request and associated task details

              PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", ti.FIRST_NAME AS "Technician", ...
            • 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 ...