drop table if exists #_bobAccountsTable#; with bobAccounts as ( select salesRepId, territoryName, divisionName, regionName, segmentPath clientSegPath, segmentName, 'OPR' partnerType, operatorId partnerId, companyName as partnerName, salesRepFirstName + ' ' + salesRepLastName as accountOwnerFullName, left( territoryPath, charIndex( divisionName, territoryPath ) + len( divisionName ) - 1 ) divisionPath, left( territoryPath, charIndex( divisionName + '/' + regionName, territoryPath ) + len( divisionName + '/' + regionName ) - 1 ) regionPath, left( territoryPath, charIndex( divisionName + '/' + regionName + '/' + territoryName, territoryPath ) + len( divisionName + '/' + regionName + '/' + territoryName ) - 1 ) 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 cmUserId salesRepId, territoryName, divisionName, regionName, null as clientSegPath, null segmentName, 'CDR' partnerType, cdr_recordId partnerId, cdr_dstName as partnerName, cmFullName accountOwnerFullName, left( territoryPath, charIndex( divisionName, territoryPath ) + len( divisionName ) - 1 ) divisionPath, left( territoryPath, charIndex( divisionName + '/' + regionName, territoryPath ) + len( divisionName + '/' + regionName ) - 1 ) regionPath, left( territoryPath, charIndex( divisionName + '/' + regionName + '/' + territoryName, territoryPath ) + len( divisionName + '/' + regionName + '/' + territoryName ) - 1 ) 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 salesRepId, territoryName, territoryPath, divisionName, divisionPath, regionName, regionPath, clientSegPath, segmentName, accountOwnerFullName, partnerType, partnerId, partnerType + ':' + cast( partnerId as varchar ) as partnerKey, partnerName into #_bobAccountsTable# from bobAccounts where #applyFilter( _accountFilters )# ; alter table #_bobAccountsTable# add constraint pk_#_bobAccountsTable# primary key ( partnerType, partnerId ); create index idx_#_bobAccountsTable#_grouping on #_bobAccountsTable# ( [#_groupingCol#], partnerType, partnerId ); 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 bob as ( select bob.[#_groupingKey#] groupingKey, bob.[#_groupingCol#] groupingName, bob.partnerId, bob.partnerType, partnerKey, count( partnerKey ) over( partition by bob.[#_groupingCol#] ) partnerCount from #_bobAccountsTable# bob ) , bobWithOpps as ( select bob.*, case when opp.opportunityId is not null then partnerKey else null end opportunityAccount, case when opp.bobStatus in ( 'ACTIVE', 'ACTIVE_PLUS' ) then partnerKey else null end activeAccount, case when opp.bobStatus = 'ACTIVE_PLUS' then partnerKey else null end activePlusAccount, case when opp.bobStatus = 'SALE' then partnerKey else null end saleAccount, case when opp.bobStatus = 'NOSALE' then partnerKey else null end noSaleAccount from bob left outer join #_bobOpportunitiesTable# opp on opp.partnerId = bob.partnerId and opp.partnerType = bob.partnerType ), bobOppSummary as ( select '#_groupingCol#' groupingType, groupingKey, groupingName, partnerCount bookOfBusinessAccounts, cast( count( distinct opportunityAccount ) as float ) opportunityAccounts, cast( count ( distinct activeAccount ) as float ) as activeAccounts, cast( count ( distinct activePlusAccount ) as float ) as activePlusAccounts, cast ( count( distinct saleAccount ) as float ) as saleAccounts , cast ( count( distinct noSaleAccount ) as float ) as noSaleAccounts from bobWithOpps group by groupingKey, groupingName, partnerCount ), bookOfBusinessReach as ( select *, case when opportunityAccounts > 0 then saleAccounts / opportunityAccounts else cast( null as float ) end as closeRate, case when bookOfBusinessAccounts > 0 then opportunityAccounts / bookOfBusinessAccounts else cast ( null as float ) end as planReach from bobOppSummary ) , opportunityData as ( select opportunityId, partnerTerritoryName, partnerName, effectiveRepEmail, estOrderDate, cases, lbs, dollars, product, shortDesc, sku, skuDesc, stageName, likelihoodPercent from tbl_DW_Opportunities#_tableSuffix# as spl with (nolock) where opportunityId in ( select opportunityId from [#_bobOpportunitiesTable#] with (nolock)) ) select * from bookOfBusinessReach opportunityData for json auto, include_null_values drop table if exists #_bobAccountsTable#; drop table if exists #_bobOpportunitiesTable#;