Query to show request details and the last notes added to that request

Query to show request details and the last notes added to that request

PGSQL & MSSQL:

SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",qd.QUEUENAME "Group",std.STATUSNAME "Request Status",sdo.NAME "Site", Longtodate(wo.CREATEDTIME) "Created Time",Longtodate(wos.LAST_TECH_UPDATE) "Last Updte Time", notes.notestext "Last Notes" FROM WorkOrder wo LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID

LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID

LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID

LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID

LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID

LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID

LEFT JOIN workorder_fields wof ON wo.workorderid=wof.workorderid

LEFT JOIN notes ON notes.workorderid=wo.workorderid

WHERE (wo.ISPARENT='1') AND notes.notesid= (select MAX(nts.notesid) from notes nts where nts.WORKORDERID=notes.WORKORDERID ) or notes.workorderid is null AND wo.CREATEDTIME >= <from_thisweek>

AND wo.CREATEDTIME <= <to_thisweek>


          • Related Articles

          • 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 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 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 last notes added in change request.

            Change Request: SELECT ad.ORG_NAME AS "Account", chdt.CHANGEID AS "Change ID", chdt.CREATEDTIME AS "Created Time", ownaaa.FIRST_NAME AS "Change Owner", priodef1.PRIORITYNAME AS "Priority", chdt.TITLE AS "Title", orgaaa.FIRST_NAME AS "Change ...