SQL查询DynamicsCRM每个帐户所有联系人的最新活动 [英] SQL Query for DynamicsCRM Most recent activity over all contact per account

问题描述

我想列出每个帐户(FilteredAccount)的最新活动(来自FilteredActivityPointer).问题是活动没有直接链接到帐户，而是在两者之间建立了联系.因此，帐户可以有多个联系人.多个联系人可以具有多个活动.

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.

完整实体模型

问题:如何获取每个帐户的最新活动?

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

我尝试了以下操作(使用SQL Server 2016，Transact SQL):

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

联系和活动之间的关系是使用AboutObjectID和AboutObjectTypeCode进行建模的

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?

推荐答案

使用 row_number()可能比使用 cross apply 更简单:

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

这篇关于SQL查询DynamicsCRM每个帐户所有联系人的最新活动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！