Query to show last notes added in request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
select wo.workorderid "request id",
max(wo.title) "subject",
max(qd.queuename) "group",
max(std.statusname) "request status",
max(pd.priorityname) "priority",
max(sdo.name) "site",
max(wo.createdtime) "created time",
max(wos.last_tech_update) "last updte time",
max(notes.notestext) "last note"
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 prioritydefinition pd on wos.priorityid=pd.priorityid
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 workordernotes won on wo.workorderid= won.workorderid
left join notes on notes.notesid=won.notesid
where (wo.isparent='1') and notes.notesid= (select max(nts.notesid) from notes nts where nts.NOTESID=notes.NOTESID ) AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
group by wo.workorderid
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?
- 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
- 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 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 notes added in change request. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 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 ...Query to show last note added in problem request. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Problem Request: SELECT ad.ORG_NAME AS "Account", prob.PROBLEMID AS "Problem ID", statdef.STATUSNAME AS "Status", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", ownaaa.FIRST_NAME AS "Technician", ...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 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 ...