Query to show last notes added in request (MSSQL & PGSQL)

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?
  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 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 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 ...
                    • Query to list out the notes added to the tickets

                      QUERY: To list of the notes added to the tickets  while generating report report SELECT  ti.FIRST_NAME AS "Support Rep", wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Contact", ...
                    • Query to show last updated field (MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) Kindly go to Reports-New Query Report and execute the below query. PGSQL: SELECT wo.WORKORDERID AS "Request ID", max(wo.TITLE) AS "Subject", max(ti.FIRST_NAME) AS "Assigned Technician", ...