SQL join two entities depending on type field

Question

In my Microsoft Dynamics CRM 2016 (on prem) using MSSQL Server 2016 I try to create a report (using ReportServer) which provides the most recent activities on all open opportunities:

I want to find the most recent activity (i.e. FilteredActivityPointer) to an opportunity (FilteredOpportunity). The first (and simple) solution to this problem (with a performant query for MSSQL) is delivered with this: SQL Query get most recent activiy per account (efficient query)

Now I need to extend the scenario like the following (traversing 2 alternative paths from activity to opportunity):

FilteredActivityPointer contains a field called referenceobjecttypecode: The field contains: * 1 for activities related to activities, in this case the field referenceobjectid contains the id of an account * 2 for activities related to contacts , in this case the field referenceobjectid contains the id of an contact

How can I extend the following query ...

SELECT opp.opportunityid, opp.name as OpportunityName, opp.statecode, opp.statecodename, fac.accountid, fac.name As AccountName, fa.regardingobjecttypecode, fa.activitytypecodename, fa.owneridname, fa.actualend As DateCompleted, fa.description As ActivityDescription
FROM FilteredAccount fac cross apply
     (SELECT TOP 1 *
      FROM FilteredActivityPointer fa
      WHERE fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.regardingobjecttypecode=1
      order by fa.actualend desc
     ) fa
JOIN FilteredOpportunity as opp
    ON fac.accountid = opp.accountid
    WHERE opp.statecode = 0

... so that I get the most recent FilteredActivityPointer either by joining accounts and joining opps OR by joining contacts , joining accounts joining accounts.

I have no idea how I can accomplish this I tried it this way but I am lost:

SELECT opp.opportunityid, opp.name as OpportunityName, opp.statecode, opp.statecodename, fac.accountid, fac.name As AccountName, fa.regardingobjecttypecode, fa.activitytypecodename, fa.owneridname, fa.actualend As DateCompleted, fa.description As ActivityDescription
FROM FilteredAccount fac cross apply
     (SELECT TOP 1 *
      FROM FilteredActivityPointer fa
      WHERE fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.regardingobjecttypecode=1
      order by fa.actualend desc
     ) fa

    FilteredAccount fac2 cross apply
     (SELECT TOP 1 *
      FROM FilteredActivityPointer fa2
      join FilteredContact as co
      ON fa2.regardingobjectid = co.contactid and fa2.regardingobjecttypecode = 2
      join FilteredAccount as ac
      on ac.accountid = opp.account.id;
      WHERE fa.statecode = 1 
      order by fa.actualend desc
     ) fa2

JOIN FilteredOpportunity as opp
    ON fac.accountid = opp.accountid
    WHERE opp.statecode = 0

Answer 1

I tried to find the most recent activity (FilteredActivity) for all open opportunities (FilteredOpportunity) in my MicrosoftDynamics CRM 2016. What sounds so simple is rather complicated because activities may be related to contacts OR to directly to opportunities. So that problem boils down to the question of how can I combine two resultsets and then select the most recent activity out of the combination of two resultsets: * one which delivers the most recent activity per opportunity * one which delivers the most recent activity per account (per contact)

This will need unions. To understand the whole problem I visualized the relationship in a diagram below.:

For resultset 1 Gordon Linoff and delivery this very efficient query: For resultset 2 GMB delivered a very clever query:

Both results are valid and very performant. Both resultsets can be designed to deliver the same structure: activities related to opportunities.

So for resultset 1 I prepared the following query:

select fac.name as accountname, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, fac.accountid, opp.opportunityid, opp.name as opportunityname
from FilteredAccount fac cross apply
     (select top (1) fa.*
      from FilteredActivityPointer fa
      where fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.activitytypecode != 10004      
      order by fa.actualend desc
     ) fa
Join FilteredOpportunity opp on opp.accountid = fac.accountid and opp.statecode = 0

and for resultset 2 I prepared the following query:

select t.name as accountname, t.actualend, t.description, t.activitytypecodename, t.activitytypecode, t.accountid, t.opportunityid, t.opportunityname
from (
    select ac.accountid, opp.name as opportunityname, opp.opportunityid, ac.name, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, 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
        and fa.activitytypecode != 10004        
    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

I tried to arrange the resultsets with equal column names.

Now i used union to combine both result sets:

select allactivities.accountname as accountname, allactivities.actualend, allactivities.description, allactivities.activitytypecodename, allactivities.activitytypecode, allactivities.accountid, allactivities.opportunityid, allactivities.opportunityname
from 
(
    (select fac.name as accountname, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, fac.accountid, opp.opportunityid, opp.name as opportunityname
    from FilteredAccount fac cross apply
         (select top (1) fa.*
          from FilteredActivityPointer fa
          where fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.activitytypecode != 10004      
          order by fa.actualend desc
         ) fa
    Join FilteredOpportunity opp on opp.accountid = fac.accountid and opp.statecode = 0
    )
Union
    (select t.name as accountname, t.actualend, t.description, t.activitytypecodename, t.activitytypecode, t.accountid, t.opportunityid, t.opportunityname
    from 
        (
        select ac.accountid, opp.name as opportunityname, opp.opportunityid, ac.name, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, 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
            and fa.activitytypecode != 10004        
        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
    )
) allactivities

results seem ok up to here.

Now I used the "over (partition)" method a second time, resulting in:

select activity.accountname, activity.actualend, activity.description, activity.activitytypecodename, activity.activitytypecode, activity.accountid, activity.opportunityid, activity.opportunityname
from
    (
        select allactivities.accountname as accountname, allactivities.actualend, allactivities.description, allactivities.activitytypecodename, allactivities.activitytypecode, allactivities.accountid, allactivities.opportunityid, allactivities.opportunityname, row_number() over(partition by allactivities.accountid order by allactivities.actualend desc) row_nr
        from 
        (
            (select fac.name as accountname, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, fac.accountid, opp.opportunityid, opp.name as opportunityname
            from FilteredAccount fac cross apply
                 (select top (1) fa.*
                  from FilteredActivityPointer fa
                  where fa.regardingobjectid = fac.accountid and fa.statecode = 1 and fa.activitytypecode != 10004      
                  order by fa.actualend desc
                 ) fa
            Join FilteredOpportunity opp on opp.accountid = fac.accountid and opp.statecode = 0
            )
        Union
            (select t.name as accountname, t.actualend, t.description, t.activitytypecodename, t.activitytypecode, t.accountid, t.opportunityid, t.opportunityname
            from 
                (
                select ac.accountid, opp.name as opportunityname, opp.opportunityid, ac.name, fa.actualend, fa.description, fa.activitytypecodename, fa.activitytypecode, 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
                    and fa.activitytypecode != 10004        
                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
            )
        ) allactivities
    ) activity
where row_nr = 1

And voila!

Please take into account that I used added "union" to the solution. The real solution to the problem above was delivered by Gordon Linoff and GMB. Without the help of those brilliant SQL-gurus, I wouldn't be able to do so!