Query to show Last added worklog of a ticket ( MSSQL )

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", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request Status", LONGTODATE(ct.createdtime) AS "Last Worklog Added Time",taskdet.TITLE AS "Task Title",taskstatus.STATUSNAME AS "Task Status", taskowner.FIRST_NAME AS "Task Owner", taskdet.PER_OF_COMPLETION AS "Percentage Of Completion", c.comment "Task Comments", Max(Cast((((ct.timespent)/1000)/3600) as varchar(20)) +'Hrs '+cast(((((ct.timespent)/1000)/60)) % 60 as varchar(20))+'Mins '+ cast((((ct.timespent)%1000)%60) as varchar(20))+ 'Secs') "Time Spent", ct.description "Worklog comment" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID" LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN workordertotaskdetails wtd on wtd.workorderid=wo.workorderid LEFT JOIN taskdetails taskdet on taskdet.taskid=wtd.taskid LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID LEFT JOIN WorkOrder_Fields wof on wof.workorderid=wo.workorderid LEFT JOIN TaskToComment tskc ON tskc.taskid = taskdet.taskid LEFT JOIN Comments c ON tskc.commentid=c.commentid LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID WHERE (std.STATUSNAME!='Closed' AND  std.STATUSNAME!='Close Requested') AND ct.chargeid=(SELECT max(chargeid) FROM workordertocharge where workorderid=wo.workorderid)   GROUP BY Wo.workorderid,pd.PRIORITYNAME,cd.CATEGORYNAME,aaU.FIRST_NAME,ti.FIRST_NAME,QD.QUEUENAME, wotodesc.FULLDESCRIPTION,std.STATUSNAME,ct.createdtime,taskdet.TITLE,Wo.TITLE,taskstatus.STATUSNAME,taskowner.FIRST_NAME,taskdet.PER_OF_COMPLETION,c.comment, ct.description ORDER BY 1

For PGSQL query, please refer this link.

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?

                    • Related Articles

                    • Query to show the last worklog added in a ticket (PGSQL)

                      Tested in build PGSQL (14300) 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", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...
                    • 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 ...
                    • 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 ...
                    • Query report to show Problem fields along with last added notes (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) 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", ...
                    • Query to show last comments added in Projects task_MSSQL

                      SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskcreatedby.FIRST_NAME AS "Created By", taskstatus.STATUSNAME AS "Task Status", taskdesc.DESCRIPTION AS "Description", ...