Query to show last conversation and last notification in a request - MSSQL

Query to show last conversation and last notification in a request - MSSQL

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

          New to ADSelfService Plus?

            • Related Articles

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

              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",  max(aau.FIRST_NAME) AS "Requester",  ...
            • Query to show last notes added in request

              PGSQL & MSSQL: 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",  Longtodate(MAX(wo.CREATEDTIME)) "Created Time", ...
            • Query to show tickets older than 30 days_MSSQL

              MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", sdo.NAME as "Site", ...