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
