Here you go Sandeep. I have rewritten it slightly different this time, this is only for the Activity types of Emails, Appointments, Tasks and Phone Calls.
SQL:
SELECT c.FullName, c.emailaddress1
FROM Contact AS c
LEFT JOIN ActivityParty AS ap ON c.ContactId = ap.PartyId
LEFT JOIN ActivityPointer AS a ON ap.ActivityId = a.ActivityId
WHERE (a.ActivityTypeCode NOT IN (4202, 4210, 4201, 4212) OR a.ActivityId IS NULL) AND c.parentcustomerid IS NULL AND c.emailaddress1 NOT LIKE '%ab.com%'
GROUP BY c.FullName, c.emailaddress1
HAVING COUNT(a.ActivityId) = 0
Fetch XML:
<fetch xmlns:generator="MarkMpn.SQL4CDS" aggregate="true">
<entity name="contact">
<attribute name="fullname" alias="fullname" groupby="true" />
<attribute name="emailaddress1" alias="emailaddress1" groupby="true" />
<link-entity name="activityparty" to="contactid" from="partyid" alias="ap" link-type="outer">
<link-entity name="activitypointer" to="activityid" from="activityid" alias="a" link-type="outer">
<attribute name="activityid" alias="a_activityid_count" aggregate="countcolumn" />
</link-entity>
</link-entity>
<filter>
<filter type="or">
<condition attribute="activitytypecode" entityname="a" operator="not-in">
<value>4202</value>
<value>4210</value>
<value>4201</value>
<value>4212</value>
</condition>
<condition attribute="activityid" entityname="a" operator="null" />
</filter>
<condition attribute="parentcustomerid" operator="null" />
<condition attribute="emailaddress1" operator="not-like" value="%ab.com%" />
<condition attribute="emailaddress1" operator="not-null" />
</filter>
<order alias="fullname" />
<order alias="emailaddress1" />
</entity>
</fetch>
Please verify this answer if it has resolved your request.
