Query to show who closed the request

Query to show who closed the request

PGSQL & MSSQL:

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>
ORDER BY 1 DESC

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>


      New to ADSelfService Plus?

      Start your free trial

        Resources

            • 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 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 tickets that shows total time spent of the ticket more than x hours

              Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) ...