Request aging with recent worklog comments (MSSQL)

Request aging with recent worklog comments (MSSQL)

Tested in MSSQL build (14306)
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>
                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Query to show both task comments and worklog comments ( MSSQL )

                      Tested in MSSQL build (14306) 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 ...
                    • Query to show Last added worklog of a ticket ( MSSQL )

                      Tested in build MSSQL (14306) 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", ...
                    • Query to show tickets older than 30 days ( MSSQL )

                      Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...
                    • Script to Disable Worklog Addition on Closed Request

                      Kindly use the below scripts under Admin -> Page Scripts to disable Worklog addition on Closed request. The below script hides the Save option while creating new worklog on closed request. Script: let params=new ...
                    • Query to show on hold status reason/comments

                      Working on Builds: 14500 and above Databases: PGSQL & 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 ...