Request aging with recent worklog comments

Request aging with recent worklog comments

MSSQL:

SELECT wo.WORKORDERID AS "Request ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type",dpt.DEPTNAME AS "Department",pd.PRIORITYNAME AS "Priority", wo.TITLE AS "Subject",wodm.Dependsonid "Depends on", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester",  LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Upd Time",std.STATUSNAME AS "Request Status", ct.description "Last Worklog comment", DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) "Days since created",wos.ISOVERDUE AS "Overdue Status" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WODependency wod ON wo.workorderid=wod.workorderid
LEFT JOIN WODependencyMarking wodm ON wod.Reqdependencyid=wodm.Reqdependencyid
LEFT JOIN ONHOLDSCHEDULER ohs on ohs.workorderid=wo.workorderid
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
WHERE (wo.ISPARENT='1') AND std.ispending = 1 AND ct.chargeid=(SELECT max(chargeid) FROM workordertocharge where workorderid=wo.workorderid) AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth>
GROUP BY wo.WORKORDERID, wo.is_catalog_template, dpt.DEPTNAME, pd.PRIORITYNAME, wo.TITLE , wodm.Dependsonid, qd.QUEUENAME, ti.FIRST_NAME , aau.FIRST_NAME ,  wo.CREATEDTIME, wos.LAST_TECH_UPDATE, std.STATUSNAME, wos.ISOVERDUE, ct.description
order by 1, "Days since created" 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>
          • Related Articles

          • 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 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 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 ...
          • Instruction to Change Worklog owner under All Request, worklog owner From Tech A to Tech B

            Scenario      Worklogs assigned to Duplicate user / Incorrect user need to change all worklogs under request  to actual Technician.  Step 1 : Navigate to Report - > New Query Report and execute the below query.  Select workorder.workorderid ...
          • Operational Hours and Differential Hours while adding a worklog

            Under Self-Service portal settings, when you have the option "Show in work log differential hours based on SLA configuration" set to "Yes", the application will look for the SLA configuration and show the operational hours accordingly while adding a ...