drop table if exists #_bobAccountsTable#; with bobAccounts as ( select ownerId, ownerType, salesRepId, segmentPath clientSegPath, 'OPR' partnerType, operatorId partnerId, 'OPR:' + cast( operatorId as varchar ) as partnerKey, companyName as partnerName, territoryPath from tbl_DW_CRMOperators#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and ownerId = 207 and ownerType = 'MFR' and classificationId in ( select classificationId from @oprClassifications ) and territoryId in ( ) and salesRepId in ( ) union all select ownerId, ownerType, cmUserId salesRepId, null as clientSegPath, 'CDR' partnerType, cdr_recordId partnerId, 'CDR:' + cast( cdr_recordId as varchar ) as partnerKey, cdr_dstName as partnerName, territoryPath from tbl_DW_CRMDistributors#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and ownerId = 207 and ownerType = 'MFR' and classificationId in ( select classificationId from @cdrClassifications ) and territoryId in ( ) and cmUserId in ( ) ) select ownerId, ownerType, salesRepId, partnerType, partnerId, partnerKey, partnerName, territoryPath, 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, effectiveRepId into #_bobOpportunitiesTable# from tbl_DW_Opportunities#_tableSuffix# with (nolock) where effectiveRepOwnerType = 'MFR' 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 ); drop table if exists #_territoryAccountsTable#; with territoryAccounts as ( select opr.ownerId, opr.ownerType, opr.salesRepId, opr.segmentPath clientSegPath, 'OPR' partnerType, opr.operatorId partnerId, 'OPR:' + cast( opr.operatorId as varchar ) as partnerKey, opr.companyName as partnerName, opr.territoryPath from tbl_DW_CRMOperators#_tableSuffix# opr with (nolock) where opr.ownerId = @ownerId and opr.ownerType = @ownerType and opr.ownerId = 207 and opr.ownerType = 'MFR' and coalesce( opr.classificationId, 0 ) not in ( select classificationId from @oprClassifications ) and territoryId in ( ) and salesRepId in ( ) union all select cdr.ownerId, cdr.ownerType, cdr.cmUserId salesRepId, null as clientSegPath, 'CDR' partnerType, cdr.cdr_recordId partnerId, 'CDR:' + cast( cdr.cdr_recordId as varchar ) as partnerKey, cdr.cdr_dstName as partnerName, cdr.territoryPath from tbl_DW_CRMDistributors#_tableSuffix# cdr with (nolock) where cdr.ownerId = @ownerId and cdr.ownerType = @ownerType and cdr.ownerId = 207 and cdr.ownerType = 'MFR' and coalesce( cdr.classificationId, 0 ) not in ( select classificationId from @cdrClassifications ) and territoryId in ( ) and cmUserId in ( ) ) with territoryAccounts1 as ( select opr.ownerId, opr.ownerType, spl.effectiveRepId as salesRepId, opr.segmentPath clientSegPath, 'OPR' partnerType, opr.operatorId partnerId, 'OPR:' + cast( opr.operatorId as varchar ) as partnerKey, opr.companyName as partnerName, opr.territoryPath from tbl_DW_CRMOperators#_tableSuffix# opr with (nolock) inner join #_bobOpportunitiesTable# spl with (nolock) on spl.partnerType = 'OPR' and spl.partnerId = opr.operatorId where opr.ownerId = @ownerId and opr.ownerType = @ownerType and opr.ownerId = 207 and opr.ownerType = 'MFR' and coalesce( opr.classificationId, 0 ) not in ( select classificationId from @oprClassifications ) and opr.territoryId in ( ) and spl.effectiveRepId in ( ) union all select cdr.ownerId, cdr.ownerType, spl.effectiveRepId salesRepId, null as clientSegPath, 'CDR' partnerType, cdr.cdr_recordId partnerId, 'CDR:' + cast( cdr.cdr_recordId as varchar ) as partnerKey, cdr.cdr_dstName as partnerName, cdr.territoryPath from tbl_DW_CRMDistributors#_tableSuffix# cdr with (nolock) inner join #_bobOpportunitiesTable# spl with (nolock) on spl.partnerType = 'CDR' and spl.partnerId = cdr.cdr_recordId where cdr.ownerId = @ownerId and cdr.ownerType = @ownerType and cdr.ownerId = 207 and cdr.ownerType = 'MFR' and coalesce( cdr.classificationId, 0 ) not in ( select classificationId from @cdrClassifications ) and cdr.territoryId in ( ) and spl.effectiveRepId in ( ) ) , territoryAccounts2 as ( select ownerId, ownerType, salesRepId, partnerType, partnerId, partnerKey, partnerName, territoryPath, clientSegPath, count(*) opportunityCount from territoryAccounts1 group by ownerId, ownerType, salesRepId, partnerType, partnerId, partnerKey, partnerName, territoryPath, clientSegPath ) , territoryAccounts3 as ( select ownerId, ownerType, salesRepId, partnerType, partnerId, partnerKey, partnerName, territoryPath, clientSegPath, row_number() over ( partition by partnerKey, salesRepId order by opportunityCount desc ) as repRank from territoryAccounts2 ) , territoryAccounts as ( select ownerId, ownerType, salesRepId, partnerType, partnerId, partnerKey, partnerName, territoryPath, clientSegPath from territoryAccounts3 where repRank = 1 ) select ownerId, ownerType, salesRepId, partnerType, partnerId, partnerKey, partnerName, territoryPath, clientSegPath into #_territoryAccountsTable# from territoryAccounts where #applyFilter( _accountFilters )# ; declare @bobAccounts float = ( select count(*) from #_bobAccountsTable# ); declare @territoryAccounts float = null; declare @territoryAccounts float = ( select count(*) from #_territoryAccountsTable# ); declare @bobAccountsWithOpps float = ( select count(*) from #_bobAccountsTable# bob with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = bob.partnerId and o.partnerType = bob.partnerType and coalesce( o.effectiveRepId, 0 ) != 5199829 ) ); declare @territoryAccountsWithOpps float = ( select count(*) from #_territoryAccountsTable# ter with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = ter.partnerId and o.partnerType = ter.partnerType ) ); declare @bobAccountsActive float = ( select count(*) from #_bobAccountsTable# bob with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = bob.partnerId and o.partnerType = bob.partnerType and o.bobStatus in ( 'ACTIVE', 'ACTIVE_PLUS' ) and coalesce( o.effectiveRepId, 0 ) != 5199829 ) ); declare @territoryAccountsActive float = ( select count(*) from #_territoryAccountsTable# ter with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = ter.partnerId and o.partnerType = ter.partnerType and o.bobStatus in ( 'ACTIVE', 'ACTIVE_PLUS' ) ) ); declare @bobAccountsActivePlus float = ( select count(*) from #_bobAccountsTable# bob with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = bob.partnerId and o.partnerType = bob.partnerType and o.bobStatus in ( 'ACTIVE_PLUS' ) and coalesce( o.effectiveRepId, 0 ) != 5199829 ) ); declare @territoryAccountsActivePlus float = ( select count(*) from #_territoryAccountsTable# ter with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = ter.partnerId and o.partnerType = ter.partnerType and o.bobStatus in ( 'ACTIVE_PLUS' ) ) ); declare @bobAccountsSale float = ( select count(*) from #_bobAccountsTable# bob with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = bob.partnerId and o.partnerType = bob.partnerType and o.bobStatus = 'SALE' and coalesce( o.effectiveRepId, 0 ) != 5199829 ) ); declare @territoryAccountsSale float = ( select count(*) from #_territoryAccountsTable# ter with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = ter.partnerId and o.partnerType = ter.partnerType and o.bobStatus = 'SALE' ) ); declare @bobAccountsNoSale float = ( select count(*) from #_bobAccountsTable# bob with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = bob.partnerId and o.partnerType = bob.partnerType and o.bobStatus = 'NOSALE' and coalesce( o.effectiveRepId, 0 ) != 5199829 ) ); declare @territoryAccountsNoSale float = ( select count(*) from #_territoryAccountsTable# ter with (nolock) where exists ( select * from #_bobOpportunitiesTable# o with (nolock) where o.partnerId = ter.partnerId and o.partnerType = ter.partnerType and o.bobStatus = 'NOSALE' and coalesce( o.effectiveRepId, 0 ) != 5199829 ) ); declare @bobCloseRate float = case when @bobAccountsWithOpps > 0 then ( @bobAccountsSale / @bobAccountsWithOpps ) else 0.0 end; declare @bobReach float = case when @bobAccounts > 0 then ( @bobAccountsWithOpps / @bobAccounts ) else 0.0 end; declare @territoryCloseRate float = case when @territoryAccountsWithOpps > 0 then ( @territoryAccountsSale / @territoryAccountsWithOpps ) else null end; declare @territoryReach float = null; declare @territoryReach float = case when @territoryAccounts > 0 then ( @territoryAccountsWithOpps / @territoryAccounts ) else null end; with data as ( select 'BOB' accountType, @bobAccounts totalAccounts, @bobAccountsWithOpps withOpps, @bobAccountsActive active, @bobAccountsActivePlus activePlus, @bobAccountsSale sale, @bobAccountsNoSale noSale, @bobCloseRate closeRate, @bobReach planReach union all select 'TER' accountType, @territoryAccounts totalAccounts, @territoryAccountsWithOpps withOpps, @territoryAccountsActive active, @territoryAccountsActivePlus activePlus, @territoryAccountsSale sale, @territoryAccountsNoSale noSale, @territoryCloseRate closeRate, @territoryReach planReach ) select * from data for json auto drop table if exists #_bobAccountsTable#; drop table if exists #_bobOpportunitiesTable#; drop table if exists #_territoryAccountsTable#;