with baseTbdCalls as ( select top 1000 i.interactionid, obj.objname, obj.publishdate, obj.unpublishdate, case when i.partnertype = 'opr' then opr.companyname else cdr.cdr_dstname end as partnerName, datediff(d,getdate(),obj.unpublishdate) as daysleft, datediff(d,obj.publishdate,getdate()) as daysActive, case when obj.objectiveid is null then 0 else 1 end as instanceCount, obj.objname + '. Days Left: ' + cast(datediff(d,getdate(),obj.unpublishdate) as varchar) as objNameDaysLeft from tbl_crm_interactions i with (nolock) left outer join tbl_opr_clientoperators opr with (nolock) on opr.operatorid = i.partnerid and opr.ownerid = i.ownerid and opr.fsltablecode = i.ownertype and i.partnertype = 'opr' left outer join tbl_cdr_distributors cdr with (nolock) on cdr.cdr_recordid = i.partnerid and cdr.ownerid = i.ownerid and cdr.fsl_tablecode = i.ownertype and i.partnertype = 'cdr' left outer join tbl_crm_interactionparticipants ip with (nolock) on ip.ownerid = i.ownerid and ip.ownertype = i.ownertype and ip.interactionid = i.interactionid left outer join tbl_bcrm_objectivetrglink otl with (nolock) on otl.ownerid = ip.ownerid and otl.ownertype = ip.ownertype and otl.linkid = ip.participantid left outer join tbl_bcrm_objectives obj with (nolock) on obj.ownerid = otl.ownerid and obj.ownertype = otl.ownertype and obj.objectiveid = otl.objectiveid and obj.unpublishdate >= getdate() where i.ownerid = and i.ownertype = and i.disposition = '?' and i.interactiondate is null and i.dispositionuserid in () and ( i.partnertype = 'opr' and opr.oprType in ( ) or i.partnertype = 'cdr' and cdr.cdr_dstcompanytype in ( ) ) and ( i.partnertype = 'opr' and opr.territoryid in ( ) or i.partnertype = 'cdr' and cdr.cdr_territoryid in ( ) ) and i.partnerType = and exists ( select * from tbl_crm_interactionsaledetails isd with (nolock) left outer join tbl_prd_skus sku with (nolock) on sku.skuid = isd.salesubjectid and sku.ownerid = isd.ownerid and sku.fsl_tablecode = isd.ownertype and isd.salesubjecttype = 'sku' left outer join tbl_prd_products prd with (nolock) on prd.prodid = isd.salesubjectid and prd.ownerid = isd.ownerid and prd.fsl_tablecode = isd.ownertype and isd.salesubjecttype = 'prd' where isd.interactionid = i.interactionid and isd.ownerid = i.ownerid and isd.ownertype = i.ownertype and ( isd.salesubjecttype = 'sku' and sku.mfrid = or isd.salesubjecttype = 'prd' and prd.mfrid = ) ) ), salesCallsUndated as ( select interactionid, partnerName, max(daysActive) as maxDaysActive, sum(instanceCount) as objCount, min(daysLeft) as minDaysLeft, STRING_AGG(objNameDaysLeft, '|') as objectiveList from baseTbdCalls where 1 = 1 and instanceCount > 0 and partnerName like group by interactionid, partnerName ) #forJSON( 'salesCallsUndated', attributes.dataStore, 'interactionid' )#