Queries to show merged requests

Queries to show merged requests

Builds: 10537, 10600 series and 13000

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

        New to ADSelfService Plus?

        Start your free trial

          Resources

              • Related Articles

              • Query to show shared requests

                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",  LONGTODATE(wo.COMPLETEDTIME) AS "Completed Time",  ...

              • 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 solution details with approver and associated requests

                MSSQL: SELECT solution.solutionid "Solution ID" , Ad.org_name "Account", max(Solution.TITLE) "Solution Title", max(au.first_name) "Solution Approver", max(KB_Topics.TOPICNAME) "Solution Topic", max(Solution_Keywords.keyword) "Keywords", ...

              • Query to show both requests and task worklog time spent together

                DB : MSSQL Timespent for both tasks and requests select wo.workorderid "Request ID", aau.FIRST_NAME "Requester", cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs '+cast(((((sum(ct.timespent))/1000)/60)) % 60 as varchar(20))+'Mins '+ ...

              • Query to show total requests and changes with its total worklog hours of a technician _ MSSQL

                MSSQL: Requests: SELECT rctd.first_name "Technician", count(wo.workorderid) "Sum of tickets handled by him", convert(varchar(5),(sum(ct.TIMESPENT))/1000/3600)+':'+convert(varchar(5),((sum(ct.TIMESPENT))/1000)%3600/60)+':'+convert(varchar(5), ...

                Related Products