drop table if exists #_bobAccountsTable#; with bobAccounts as ( select salesRepId, segmentPath clientSegPath, 'OPR' partnerType, operatorTk as partnerTk, operatorId partnerId, companyName as partnerName, territoryPath, salesRepFirstName + ' ' + salesRepLastName as accountOwnerFullName, lastInteractionDate, priority from tbl_DW_CRMOperators#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and ownerId = 207 and ownerType = 'MFR' and territoryId in ( ) and salesRepId in ( ) and classificationId in ( select classificationId from @oprClassifications ) union all select cmUserId salesRepId, null clientSegPath, 'CDR' partnerType, cdr_recordIdTk as partnerTk, cdr_recordId partnerId, cdr_dstName as partnerName, territoryPath, cmFullName accountOwnerFullName, lastInteractionDate, cdr_priority as priority from tbl_DW_CRMDistributors#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and ownerId = 207 and ownerType = 'MFR' and territoryId in ( ) and cmUserId in ( ) and classificationId in ( select classificationId from @cdrClassifications ) ) select salesRepId, accountOwnerFullName, partnerType, partnerId, partnerTk, partnerType + ':' + cast( partnerId as varchar ) as partnerKey, partnerName, territoryPath, lastInteractionDate, priority, clientSegPath into #_bobAccountsTable# from bobAccounts where #applyFilter( _accountFilters )# ; drop table if exists #_bobOpportunitiesTable#; select partnerId, partnerType, opportunityId, estOrderDate, productHierarchyPath, case when statusCode = 'R' then 'NOSALE' when statusCode = 'C' then 'SALE' when stageId in ( ) then 'ACTIVE_PLUS' when stageId in ( ) then 'ACTIVE' else 'EARLY' end as bobStatus into #_bobOpportunitiesTable# from tbl_DW_Opportunities#_tableSuffix# with (nolock) where effectiveRepOwnerType = 'MFR' and coalesce( effectiveRepId, 0 ) != 5199829 and stageId in ( ) and #applyFilter( _opportunityFilters )# order by opportunityId alter table #_bobOpportunitiesTable# add constraint pk_#_bobOpportunitiesTable# primary key ( opportunityId ); create index idx_#_bobOpportunitiesTable# on #_bobOpportunitiesTable# ( partnerType, partnerId, bobStatus ); with noReach as ( select partnerId, partnerTk, partnerType, partnerName, lastInteractionDate, priority from #_bobAccountsTable# bob where not exists ( select * from #_bobOpportunitiesTable# opp where opp.partnerId = bob.partnerId and opp.partnerType = bob.partnerType and opp.bobStatus in ( 'ACTIVE', 'SALE', 'NOSALE', 'ACTIVE_PLUS' ) ) ) select * from noReach order by partnerName for json auto, include_null_values drop table if exists #_bobAccountsTable#; drop table if exists #_bobOpportunitiesTable#;