Query to show last conversation and last notification in a request ( MSSQL )

Query to show last conversation and last notification in a request ( MSSQL )

Tested in Build MSSQL (14306)

Query

SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
wo.TITLE "Subject",
ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
(select  conversationdescription.description from conversationdescription left join conversation con1 on conversationdescription.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid) "Last requester response",
(select notificationtodesc.description from notificationtodesc left join notification no1 on notificationtodesc.notificationid=no1.notificationid left join notify_workorder nwo1 on no1.notificationid=nwo1.notificationid where no1.notificationid in (select max(notification.notificationid) from notification left join notify_workorder on notification.notificationid=notify_workorder.notificationid group by notify_workorder.workorderid) and nwo1.workorderid=wo.workorderid) "Last Tech reply" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 std.ispending='true'
and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,'2019-11-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,'2019-11-30 23:59',21)  
order by 1

With Conversation timestamps

SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
wo.TITLE "Subject",
cd.CATEGORYNAME "Category", 
ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
(select conversationdescription.description from conversationdescription left join conversation con1 on conversationdescription.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid) "Last requester response",
longtodate((select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(con1.createdtime/1000),'1970-01-01 00:00:00') from conversationdescription left join conversation con1 on conversationdescription.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid)) "Last requester response Time",
(select notificationtodesc.description from notificationtodesc left join notification no1 on notificationtodesc.notificationid=no1.notificationid left join notify_workorder nwo1 on no1.notificationid=nwo1.notificationid where no1.notificationid in (select max(notification.notificationid) from notification left join notify_workorder on notification.notificationid=notify_workorder.notificationid group by notify_workorder.workorderid) and nwo1.workorderid=wo.workorderid) "Last Tech reply",
(select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(no1.notificationdate/1000),'1970-01-01 00:00:00') from notificationtodesc left join notification no1 on notificationtodesc.notificationid=no1.notificationid left join notify_workorder nwo1 on no1.notificationid=nwo1.notificationid where no1.notificationid in (select max(notification.notificationid) from notification left join notify_workorder on notification.notificationid=notify_workorder.notificationid group by notify_workorder.workorderid) and nwo1.workorderid=wo.workorderid) "Last Tech reply Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.ispending='true' and  wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>


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>
    • This year - <from_thisyear> - <to_thisyear>
    • Last year - <from_lastyear> - <to_lastyear>






      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Query to show conversation description (MSSQL & PGSQL)

                      Database: MSSQL and PGSQL Tested in build PGSQL (14300) and MSSQL (14306) It is not recommended to use the query often in business hours as it may cause performance issues since it has to pull huge data. Execute the below query under Reports->New ...

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

                    • 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) ...

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

                      Related Products