Query to show Last added worklog of a ticket

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 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


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>

o    <from_thisweek> - Starting date of this week

o    <to_thisweek> - Ending date of this week

2.     Available Date Templates

o    Today - <from_today> - <to_today>

o    This week - <from_thisweek> - <to_thisweek>

o    Last week - <from_lastweek> - <to_lastweek>

o    This month - <from_thismonth> - <to_thismonth>

o    Last month - <from_lastmonth> - <to_lastmonth>

o    This quarter - <from_thisquarter> - <to_thisquarter>

o    Last quarter - <from_lastquarter> - <to_lastquarter>

o    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 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 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 ...
          • 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", ...