SQL Query for Ticket Resolution Notes
I'm running the query below to create a quick report but would like to enhance it to include the resolution notes. Which table has the resolution notes? My goal is to make sure tickets are being closed with a few notes regarding the steps taken to resolve the issue.
Thanks for your help
SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
cd.CATEGORYNAME "Category",
wo.TITLE "Subject",
ti.FIRST_NAME "Technician",
pd.PRIORITYNAME "Priority",
std.STATUSNAME "Request Status",
std.ISPENDING "Pending Status",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00')"Completed Time",
wo.TIMESPENTONREQ "Time Elapsed",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wos.LAST_TECH_UPDATE/1000),'1970-01-01 00:00:00') "Last Updated Time",
wotodesc.FULLDESCRIPTION "Description" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,'2016-01-01 00:00',21)
and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,'2017-01-01 23:59',21)
AND wo.ISPARENT='1'
order by 1 desc