Where to find all activities of a account in crm 2011 SQL

Question

I'm trying to find all activities in crm 2011 which are in any way related to a specific account(Company). How can I Archive that? I know that ActivityPointerBase contains all activities, but I'm not sure that I get all the activities for a specific account if I filter with OwnerId or OwningBusinessUnit. Are there any more tables or fields that contain additional Information I could use?

Accepted Answer

For each activity type in CRM there are 3 views that make up the activity. There is the `ActivityPointer` view, `ActivityParty` view, and the actual view for the specific activity such as `Fax` or `phonecall`.

The `ActivityParty` view holds all the relationships to activities that are out of the box. So if you are looking for any activity that is related via a native lookup you can do a join to the `ActivityParty` view.

The sql would look like this:

```
select distinct(pointer.ActivityId) from ActivityPointer pointer 
inner join ActivityParty party on pointer.ActivityId = party.ActivityId and 
party.PartyId= @accountId
order by  pointer.ActivityId
```

if you needed to use a custom relationship on a specific entity you would have to go to the actual activity view `Fax`, `Phonecall`, etc.

If you want to know exactly which lookup the account is related to, there is a `ParticipationTypeMask` that is on the `ActivityParty` entity that you can use to determine if they are the sender, regarding, to, bcc, etc. Here is the link to the SDK article.

Where to find all activities of a account in crm 2011 SQL

Solution

Problem