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 ADSelfService Plus?

                    • Related Articles

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