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
          • 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 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 ...
          • Query to show active and archived tickets

            Below is only for MSSQL. For PGSQL query, refer comments section SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",serdef.NAME "Service Category",wo.TITLE "Subject",sdo.NAME "Site",accountdefinition.org_name ...