Query to show conversation description (MSSQL & PGSQL)

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 Report and let us know if that helps.

Please note, incoming and outgoing conversations are shown separately, cannot be merged together.

Incoming:

select wo.workorderid "Request ID", convd.description "Conversation Description" FROM workorder wo LEFT JOIN conversation conv ON wo.workorderid=conv.workorderid LEFT JOIN conversationdescription convd ON conv.conversationid=convd.conversationid LEFT JOIN sduser sdu ON conv.requesterid=sdu.userid LEFT JOIN aaauser aaau ON sdu.userid=aaau.user_id WHERE conv.workorderid=wo.workorderid AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth> group by wo.workorderid, convd.description 
order by 1

Outgoing:

select wo.workorderid "Request ID", ntd.description "Notification Description" from workorder wo left join notify_workorder nw on wo.workorderid=nw.workorderid LEFT JOIN notification n on nw.notificationid=n.notificationid LEFT JOIN notificationtodesc ntd ON n.notificationid=ntd.notificationid left join aaauser nau on n.senderid=nau.user_id where nau.first_name!='system'  AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth> GROUP BY wo.workorderid, ntd.description

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>
Last tested on Build 14500 and above

                  New to ADSelfService Plus?