Query to show the last worklog added in a ticket

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 second' as varchar)) "Time Spent (In HH:MM:SS)",
       Longtodate(max(ct.TS_STARTTIME)) "Time Spent Starttime",
       max(rctd.FIRST_NAME) "Time Spent Technician",
       max(ct.DESCRIPTION) "Time Spent Description",
       max(std.STATUSNAME) "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE (wo.ISPARENT='1')
  AND ct.chargeid=
    (SELECT max(chargeid)
     FROM workordertocharge
     WHERE workorderid=wo.workorderid)
  AND wo.createdtime >= <from_thisweek>
  AND wo.createdtime <= <to_thisweek>
GROUP BY wo.WORKORDERID

For MSSQL Query, please refer this link.


NOTE: You can also replace the Period range(highlighted in the query) from the below available template.


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 Last added worklog of a ticket _MSSQL

            MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", wotodesc.FULLDESCRIPTION AS ...
          • 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 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 both task comments and worklog comments

            MSSQL: SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category",  "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS "Owner", "taskdet"."TITLE" ...
          • Query report to show Problem fields along with last added notes

            PGSQL & MSSQL: SELECT prob.PROBLEMID AS "Problem ID", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", orgaaa.FIRST_NAME AS "Reported by", LONGTODATE(prob.DUEBYTIME) AS "DueBy Date", LONGTODATE(prob.CLOSEDTIME) AS "Closed Date", ad.ORG_NAME AS ...