Hi
Try this :
select A.name,X.name, ( select top 1 createdon from FilteredPhoneCall B join FilteredActivityParty C on B.activityid = C.activityid where C.partyid = A.accountid order by B.createdon desc) as PhonecallDate,
( select top 1 createdon from FilteredEmail D join FilteredActivityParty E on D.activityid = E.activityid where E.partyid = A.accountid order by D.createdon desc ) as EmailDate,
X.estimatedvalue as Revenue, X.accountidname
from FilteredAccount A
left join FilteredOpportunity X
on A.accountid = X.accountid
order by A.name
1. This will list activities where company is a direct party in Either To, From or Regarding fields. Activities on Companies contacts, qualified leads will not be listed in this.
2. Regarding opportunity, there can be multiple opportunities on one company. So which one do you want to pick ? This query will give one row per opportunity. So if there will be multiple opportunity, you will have multiple rows for same account.
