Query to show who closed the request

Query to show who closed the request


select wo.workorderid "Request ID", aau.first_name "Request Closed By", cit.typename "Role", queuedefinition.queuename "Group" from WorkOrder wo  LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID LEFT JOIN aaauser aau ON woh.operationownerid=aau.user_id LEFT JOIN sduser sdu ON aau.user_id=sdu.userid LEFT JOIN ci ci  ON sdu.ciid=ci.ciid LEFT JOIN citype cit ON cit.typeid=ci.citypeid left join workorder_queue woq
on wo.workorderid=woq.workorderid left join queuedefinition on woq.queueid=queuedefinition.queueid WHERE woh.operation='CLOSE' AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
          • Related Articles

          • Query to show the last worklog added in a ticket

            PGSQL: SELECT wo.WORKORDERID "Request ID",        max(aau.FIRST_NAME) "Requester",        max(wo.TITLE) "Subject",        max(qd.QUEUENAME) "Group",        max(ti.FIRST_NAME) "Assigned Technician",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show ticket aging - PGSQL

            PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item",  cri.FIRST_NAME AS "Created By", rtd.name ...
          • Query to show Last added worklog of a ticket

            PGSQL: SELECT wo.WORKORDERID "Request ID",        max(aau.FIRST_NAME) "Requester",        max(wo.TITLE) "Subject",        max(qd.QUEUENAME) "Group",        max(ti.FIRST_NAME) "Assigned Technician",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show ticket first assign/pick up time _ PGSQL

            PGSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",ad.org_name "Account", ...
          • Number of open and closed tasks per ticket

            MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "icd"."NAME" AS "Item", "wo"."TITLE" ...