Customer experience | Sales, Customer Insights,...

How to write DB query to get Email data associated with a ticket

(0) Share

Report

Posted on by Email data

Hello Team,

I need to write SQL queries to get all email data associated with a ticket. Can you please help? below the query, I wrote, but for sent item TO and From data is showing wrong.

SELECT
AP.Subject As Subject
,AP.Sender AS [From]
,AP.ToRecipients AS [To]
--,I.Description
,AP.Description
,I.Title AS [Regarding]
,I.prioritycodename AS Priority
, Case WHEN AP.statuscode = 1
THEN 'Draft'
WHEN AP.statuscode = 2
THEN 'Completed'
WHEN AP.statuscode = 3
THEN 'Sent'
WHEN AP.statuscode = 4
THEN 'Received'
WHEN AP.statuscode = 5
THEN 'Canceled'
WHEN AP.statuscode = 6
THEN 'Pending Send'
WHEN AP.statuscode = 7
THEN 'Sending'
ELSE 'Failed'
END AS [Status Reason]
,DATEADD( HOUR,8,AP.ModifiedOn) AS [Modified On]
,I.TicketNumber [Case Number]
,I.Title AS [Title]
,I.statecodename AS [Status (Regarding)]
,AM.ActivityMimeAttachmentId
FROM FilteredIncident I WITH(NOLOCK)
INNER JOIN ActivityPointerBase AP WITH(NOLOCK)
ON I.IncidentId = AP.RegardingObjectId
LEFT JOIN ActivityMimeAttachment AM
ON Am.ObjectId = AP.OwnerId
LEFT JOIN Attachment A WITH(NOLOCK)
ON A.AttachmentId = Am.AttachmentID
WHERE (AP.Subject <> 'Closed' )
AND TicketNumber = @TicketNumber

All responses (8)

Answers (0)

Email data 5 on at

Like (0)

Report

RE: How to write DB query to get Email data associated with a ticket

No problem. Thanks for your support. I will continue my research on the same by using your input.
a33ik 84,325 Most Valuable Professional on at

Like (1)

Report

RE: How to write DB query to get Email data associated with a ticket

I'm sorry, I will not write queries for you. All the pointers you need are located in my replies. Good luck.
Email data 5 on at

Like (0)

Report

RE: How to write DB query to get Email data associated with a ticket

Thanks for your reply. I am working on DB migration task and need to write DB query to generate email data for the below fields:

Subject , From, To, Description, Regarding, Priority, Status Reason, Modified On, Case Number (Regarding), Title (Regarding), Status (Regarding)

The query I wrote is showing all the email details per ticket but for the Sent item From and To field, data are incorrect.

I am very confused and really need urgent help to write DB query. PLease help.
Suggested answer

a33ik 84,325 Most Valuable Professional on at

Like (1)

Report

RE: How to write DB query to get Email data associated with a ticket

Hello,

I'm afraid AddressUsed is used in case of unresolved emails only - niravpancholi.wordpress.com/.../

If you want to get an email of the real record (like user/account/contact/e.t.c.) you will have to join to correspond view and use the field of it.

Also instead of join to ActivityParty, I would recommend using subqueries instead - docs.microsoft.com/.../subqueries
Email data 5 on at

Like (0)

Report

RE: How to write DB query to get Email data associated with a ticket

Thanks. I have pasted my sql query below. I am still unable to find the relationship and sender/to field value for a ticket email data. Can you please review and help me ?

SELECT
CASE WHEN APR.ParticipationTypeMask = 1 THEN APR.AddressUsed END AS [From]
,CASE WHEN APR.ParticipationTypeMask = 2 THEN APR.AddressUsed END AS [To]
--,AP.Sender AS [From]
--,AP.ToRecipients AS [To]
FROM FilteredIncident I WITH(NOLOCK)
INNER JOIN ActivityPointerBase AP WITH(NOLOCK)
ON I.IncidentId = AP.RegardingObjectId
LEFT JOIN ActivityMimeAttachment AM
ON Am.ObjectId = AP.OwnerId
LEFT JOIN Attachment A WITH(NOLOCK)
ON A.AttachmentId = Am.AttachmentID
INNER JOIN ActivityParty APR
ON APR.ActivityId = AP.ActivityId
WHERE (AP.Subject <> 'Closed' )
AND TicketNumber = @TicketNumber
a33ik 84,325 Most Valuable Professional on at

Like (1)

Report

RE: How to write DB query to get Email data associated with a ticket

Can you please post your updated query where you use ActivityParty?
Email data 5 on at

Like (0)

Report

RE: How to write DB query to get Email data associated with a ticket

Thanks for your reply. I checked but still, I am not getting the right value. Can you please help to review my query?
Suggested answer

a33ik 84,325 Most Valuable Professional on at

Like (1)

Report

RE: How to write DB query to get Email data associated with a ticket

Hello,

In order to get "From" and "To" you should use ActivityParty entity - docs.microsoft.com/.../activityparty-entity