declare @cutoffDate as datetime set @cutoffDate = ( select top 1 whitespaceCutoffDate from tbl_DW_1fsUniverse#_tableSuffix# with (nolock)); with operatorsWithPurchasing as ( select distinct c.cho_id , c.purchasingMonth, c.ownerId, c.ownerType, c.cho_type from tbl_DW_OperatorAnalyzer#_tableSuffix# c with (nolock) where c.cho_type in ( 'I', 'O' ) and c.purchasingMonth >= @cutoffDate and c.ownerId = #attributes.ownerId# and c.ownerType = '#attributes.ownerType#' and c.skuId in ( ) ), buyingFilter as ( select distinct c.cho_id , c.purchasingMonth, c.ownerId, c.ownerType, c.cho_type from tbl_DW_OperatorAnalyzer#_tableSuffix# c with (nolock) where c.cho_type in ( 'I', 'O' ) and c.purchasingMonth >= @cutoffDate and c.ownerId = #attributes.ownerId# and c.ownerType = '#attributes.ownerType#' and c.skuId in ( ) ), result as ( select u.ownerId, u.ownerType, u.operatorType, u.operatorChoId, u.operatorName,u.operatorAddress, u.operatorCity, u.operatorState, u.operatorZipCode, u.operatorCounty, u.operatorTerritory, u.operatorTerritoryId, u.mainSegment, u.subSegment, u.operatorSegment, u.segmentId, u.averageClientVolume industryWeight, u.operatorTerritoryPath, coalesce( u.primaryDstName, u.primaryParentDstName ) primaryDstName, coalesce( u.primaryDstSkuSales, 'N' ) primaryDistributorCustomer, coalesce( u.secondaryDstName, u.secondaryParentDstName ) secondaryDstName, coalesce( u.secondaryDstSkuSales, 'N' ) secondaryDistributorCustomer, case when u.primaryDstSkuSales = 'Y' and u.secondaryDstSkuSales = 'Y' then 'Prim. & Sec.' when u.primaryDstSkuSales = 'Y' then 'Primary' when u.secondaryDstSkuSales = 'Y' then 'Secondary' else 'None' end distributorAccess, secondaryParentDstId, secondaryDstId, primaryParentDstId, primaryDstId, numberOfPurchasingSources, case when exists ( select * from operatorsWithPurchasing bo where bo.cho_id = u.operatorChoId ) then 'N' else 'Y' end whitespace, u.operatorPurchasing as operatorCustomer, averageClientVolume / 12.0 estMonthlySpend, -- round( PERCENT_RANK() OVER (ORDER BY u.averageClientVolume) * 100.0, 2 ) as rank -- ROW_NUMBER() OVER (ORDER BY u.averageClientVolume desc) as rank u.rank1fs as rank, u.percentScore as score, u.masterRank as masterRank from tbl_DW_1fsUniverse#_tableSuffix# u with (nolock) where u.clientCount > 0 and u.operatorType in ( 'CHAINHQ', 'INDEPENDENT' ) and u.lastMonth >= @cutoffDate and exists( select * from tbl_DW_1fSOperatorMemberships#_tableSuffix# m where m.ownerId = u.ownerId and m.ownerType = u.ownerType and m.memberChoId = u.operatorChoId and not ( m.deaffiliationDate < u.whitespaceCutoffDate and m.affiliationDate > getDate() ) and m.memberGroupChoId in ( ) ) and exists ( select * from buyingFilter bf where bf.cho_Id = u.operatorChoId ) ) select * from result where whitespace = 'Y' and 1 = 1 and operatorTerritoryId in ( ) and segmentId in ( ) and primaryParentDstId primaryDstId = and 1 = 2 order by rank -- desc for json path, include_null_values