Queries to show merged requests (PGSQL & MSSQL)

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

          New to ADSelfService Plus?