SQL Query for DynamicsCRM Most recent activity over all contact per account

Question

I want to list the most recent activity (from FilteredActivityPointer) per account (FilteredAccount). Problem is that activities are not directly linked to account but have contacts in between. So accounts can have multiple contacts. Multiple contacts can have multiple activites.

The full entity model

Question: How can I retrieve the most recent activity per account?

I tried the following (using SQL Server 2016, Transact SQL):

SELECT *
FROM FilteredContact co cross apply
    (SELECT TOP 1 *
    FROM FilteredActivityPointer fa
    where fa.regardingobjectid = co.contactid and fa.regardingobjecttypecode=2
    order by fa.actualend desc
) fa
JOIN FilteredAccount ac on ac.accountid = co.accountid 
JOIN FilteredOpportunity opp on opp.accountid = ac.accountid and opp.statecode=0

The relationship between contact and activity is modelled using regardingobjectid and regardingobjecttypecode

My problem is that the query above lists the most recent activity per contact NOT per account. How can determine the most recent activity over all contacts of one account?

Answer 1

This might be simpler adressed with row_number() than cross apply:

select *
from (
    select *, row_number() over(partition by ac.accountid order by fa.actualend desc) rn
    from FilteredContact co 
    inner join FilteredActivityPointer fa
        on  fa.regardingobjectid = co.contactid 
        and fa.regardingobjecttypecode = 2
    inner join FilteredAccount ac 
        on  ac.accountid = co.accountid 
    inner join FilteredOpportunity opp 
        on  opp.accountid = ac.accountid 
        and opp.statecode = 0
) t
where rn = 1