locked
CRM 4.0 SQL Query to retrieve all phone calls of contacts RRS feed

  • Question

  • I'm having a hard time getting the phone calls of contacts.  In Advanced find I can find it. first by selecting activity parties then phone calls.

    Would anyone care to share some sql please.

    My purpose for this is to create a neglected contacts report.


    David Withers
    Tuesday, November 8, 2011 6:19 PM

Answers

  • I can get you started with this:
    SELECT fc.firstname, FC.lastname, FAP2.activitypartyid, fap.* 
    FROM FilteredActivityPointer AS fap
    LEFT JOIN FilteredActivityParty AS FAP2 ON fap.activityid = FAP2.activityid
    RIGHT JOIN FilteredContact AS FC ON FAP2.partyid = FC.contactid
    WHERE FAP.activitytypecodename = 'Phone Call'
    AND FAP2.participationtypemask = 2
    
    It's probably not what you're looking for, but it's a start. Otherwise there is a standard "neglected accounts" reports which you can download and see if you can get some useful out of.
    • Marked as answer by David Withers Tuesday, November 8, 2011 10:05 PM
    Tuesday, November 8, 2011 9:22 PM

All replies

  • I can get you started with this:
    SELECT fc.firstname, FC.lastname, FAP2.activitypartyid, fap.* 
    FROM FilteredActivityPointer AS fap
    LEFT JOIN FilteredActivityParty AS FAP2 ON fap.activityid = FAP2.activityid
    RIGHT JOIN FilteredContact AS FC ON FAP2.partyid = FC.contactid
    WHERE FAP.activitytypecodename = 'Phone Call'
    AND FAP2.participationtypemask = 2
    
    It's probably not what you're looking for, but it's a start. Otherwise there is a standard "neglected accounts" reports which you can download and see if you can get some useful out of.
    • Marked as answer by David Withers Tuesday, November 8, 2011 10:05 PM
    Tuesday, November 8, 2011 9:22 PM
  • I think that will work ! :)

    thank you

     

     

    I already tried modifying the default reports, and there's just too much.


    David Withers
    Tuesday, November 8, 2011 10:05 PM