locked
CRM On Premise - Find Contact not used in Email Communcation RRS feed

  • Question

  • Hi Forum,

    I have a requirement where I need to give list of the Contacts which has never been used in email communication. i.e not used in either 'From', 'To' , 'CC' or 'BCC' field in email activity record.

    As we are on premise deployment I just would like your help to get SQL query to find this list.

    Thanks

    H.Desai


    • Edited by H.Desai Tuesday, December 6, 2016 12:53 AM
    Tuesday, December 6, 2016 12:41 AM

Answers

  • Hello,

    Following query should cover your requirement:

    Select
    	c.fullname
    	,c.contactid
    From FilteredContact c
    Where not exists (
    	select 1 
    	from FilteredActivityParty fap
    	Inner Join FilteredActivityPointer fa on fap.activityid = fa.activityid 
    		and fa.activitytypecode = 4202 
    		and fap.partyid = c.contactid)


    Dynamics CRM MVP
    Read My blog
    Subscribe for one of my courses

    • Marked as answer by H.Desai Tuesday, December 6, 2016 2:40 AM
    Tuesday, December 6, 2016 1:12 AM
    Moderator

All replies

  • Hello,

    Following query should cover your requirement:

    Select
    	c.fullname
    	,c.contactid
    From FilteredContact c
    Where not exists (
    	select 1 
    	from FilteredActivityParty fap
    	Inner Join FilteredActivityPointer fa on fap.activityid = fa.activityid 
    		and fa.activitytypecode = 4202 
    		and fap.partyid = c.contactid)


    Dynamics CRM MVP
    Read My blog
    Subscribe for one of my courses

    • Marked as answer by H.Desai Tuesday, December 6, 2016 2:40 AM
    Tuesday, December 6, 2016 1:12 AM
    Moderator
  • Thanks Andrii,

    Appreciate your quick reply.

    I was working on much simpler query to achieve that which is:

    SELECT Fullname,ContactId From Contact C WHERE  ContactId NOT IN (Select partyid from ActivityParty)
     

     Thinking behind this was that, PartyID of ActvityParty view is actual GUID of the relevant object ( in my case Contact) and I can straight away use that but it was not returning any row so there is something fundamentally wrong in that. Not using ActivityPointer probably the reason.

    By the way your query is still running right now in my environment which has fetched 2425 contacts and still going on for more than 10 minutes. I think this is due to the complex relationship among few Activity related entities. 

    Anyway, thanks again for your time

    Cheers,

    H.Desai


    • Edited by H.Desai Tuesday, December 6, 2016 2:43 AM
    Tuesday, December 6, 2016 2:39 AM