MS-CRM - Query to list the Activity record distribution across Business Units in the CRM database

The MS-CRM contains many entities of type Activity. Few of the most widely used activities are PhoneCall, Email, Appointment etc..

When the activity record is created in MS-CRM, it internally loads 3 tables with the provided information

1. Base table for the Activity. Ex. - AppointmentBase
2. ActivityPointerBase
3. ActivityPartyBase

ActivityPointerBase table contains one record for each activity record that is created in the MS-CRM. While ActivityPartyBase table hols the information of the parties (participants) for particular Activity record.

Following tables are used in the query to find the distribution of the activities across 

• BusinessUnitbase - This stores the information related to business units created in your MS-CRM system

• Entitybase - This table stores the information related to entities defined in the CRM. It contains the column named ObjectTypeCode which represents the unique integer number assigned for the entity in the MS-CRM.

• ActivityPointerBase

;WITH Activities AS
(

SELECT 
OwningBusinessUnit
,ActivityTypeCode
,COUNT(1) RecCount
FROM ActivityPointerBase apb
GROUP BY OwningBusinessUnit,ActivityTypeCode
)
SELECT 
DISTINCT 
BU.Name AS BusinessUnit
,ENT.Name AS Entity
,ACT.RecCount
FROM Activities ACT
LEFT OUTER JOIN BusinessUnitBase Bu
ON Bu.BusinessUnitId = ACT.OwningBusinessUnit
LEFT OUTER JOIN MetadataSchema.Entity Ent
ON Ent.ObjectTypeCode = ACT.ActivityTypeCode
ORDER BY BusinessUnit,Entity