---> #attributes.dataStore.sqlOrderBy#order by rank /* #SerializeJSON( _objectParams )# arrayLen( _objectParams.buyingProductHierarchyPath ) gt 0 = #arrayLen( _objectParams.buyingProductHierarchyPath ) gt 0# arrayLen( _objectParams.buyingProductSetTags ) gt 0 = #arrayLen( _objectParams.buyingProductSetTags ) gt 0# _objectParams.buyingSKUId neq "" = #_objectParams.buyingSKUId neq ""# _objectParams.buyingProdId neq "" = #_objectParams.buyingProdId neq ""# ArrayLen( _objectParams.buyingMfrId ) gt 0 = #ArrayLen( _objectParams.buyingMfrId ) gt 0# _objectParams.buyingAnything = #_objectParams.buyingAnything# _buyingFilter = #_buyingFilter#; _objectParams.operatorListId = #_objectParams.operatorListId#: #_objectParams.operatorListId eq ""# _objectParams.operatorTerritoryId = #_objectParams.operatorTerritoryId#: #_objectParams.operatorTerritoryId eq ""# ArrayLen( _objectParams.operatorTerritoryPath ) = #ArrayLen( _objectParams.operatorTerritoryPath )#: #ArrayLen( _objectParams.operatorTerritoryPath ) eq 0# _objectParams.operatorSegment = #_objectParams.operatorSegment#: #_objectParams.operatorSegment eq ""# _objectParams.distributorKeyId = #_objectParams.distributorKeyId#: #_objectParams.distributorKeyId eq ""# _objectParams.memberGroupChoId = #_objectParams.memberGroupChoId#: #_objectParams.memberGroupChoId eq ""# not _productFilter = #not _productFilter#: #not _productFilter# _scopeFilter = #_scopeFilter# condition = #_objectParams.operatorListId eq "" and _objectParams.operatorTerritoryId eq "" and ArrayLen( _objectParams.operatorTerritoryPath ) eq 0 and _objectParams.operatorSegment eq "" and _objectParams.distributorKeyId eq "" and _objectParams.memberGroupChoId eq ""# _returnData = #_returnData# _noDataTrace = #_noDataTrace# _objectParams.selectionStatus = #_objectParams.selectionStatus# */ declare @cutoffDate as datetime set @cutoffDate = ( select top 1 whitespaceCutoffDate from tbl_DW_1fsUniverse#_tableSuffix# with (nolock)); drop table if exists #_operatorsWithPurchasingTable#; select distinct c.cho_id into #_operatorsWithPurchasingTable# 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 1 = 2 and ( 1 = 2 or c.productHierarchyPath like ) and ( 1 = 2 or c.productSetTags like ) and c.skuId in ( ) and c.prodId in ( ) ---> and ( 1 = 2 or 1 = 1 or c.ownerIdc.mfr_id = ) and c.territoryId in ( ) ; alter table #_operatorsWithPurchasingTable# add constraint pk_#listRest( _operatorsWithPurchasingTable, "_")# primary key ( cho_id ); drop table if exists #_buyingTable#; select distinct c.cho_id into #_buyingTable# 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 1 = 1 and ( 1 = 2 or c.productHierarchyPath like ) and ( 1 = 2 or c.productSetTags like ) and c.skuId in ( ) and c.prodId in ( ) ---> and ( 1 = 2 or c.mfr_id = ) and c.territoryId in ( ) ; alter table #_buyingTable# add constraint pk_#listRest( _buyingTable, "_")# primary key ( cho_id ); with operatorsWithPurchasing as ( select * from #_operatorsWithPurchasingTable# with (nolock) ), tastewiseDataRanked as ( select row_number() over ( partition by normalized_id order by menuScore desc ) as ranking, restaurantId, normalized_id, menuScore from [#_tastewiseIdTable#] with (nolock) ), tastewiseData as ( select * from tastewiseDataRanked with (nolock) where ranking = 1 ), buyingFilter as ( select * from #_buyingTable# with (nolock) , 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 1 = 1 and ( 1 = 2 or c.productHierarchyPath like ) and ( 1 = 2 or c.productSetTags like ) and c.skuId in ( ) and c.prodId in ( ) ---> and ( 1 = 2 or c.mfr_id = ) and c.territoryId 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 #_operatorsWithPurchasingTable# bo where bo.cho_id = u.operatorChoId ) then 'N' else 'Y' end whitespace, u.operatorPurchasing as operatorCustomer, -- u.averageClientVolume / 12.0 estMonthlySpend, -- TSE-572 u.averageClientVolume as estMonthlySpend, u.estMonthlyVolume, u.estMonthlyVolumeStatus, -- 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, case when st.cho_id is not null then 1 else 0 end as selectedOperator, u.inClientLibrary, u.clientOperatorId, u.lastInteractionDate, u.accountOwnerUserId, u.accountOwnerFullName, u.salesRepId, u.salesRepFirstName, u.salesRepLastName, u.salesRepFirstName + ' ' + u.salesRepLastName as salesRepFullName, coalesce( tw.menuScore, 0.0 ) * 100.0cast( null as float ) as menuScore from tbl_DW_1fsUniverse#_tableSuffix# u with (nolock) innerleft outer join #_currentSelectionsTable# st with (nolock) on st.cho_id = u.operatorChoId inner join tastewiseData tw with (nolock) on tw.normalized_id = u.tastewiseId where u.operatorType in ( 'CHAINHQ', 'INDEPENDENT' ) and (( u.clientCount > 0 and u.lastMonth >= @cutoffDate ) or( u.clientCount is null and u.lastMonth is null and u.tastewiseId is not null ) ) and u.operatorTerritoryId in ( ) and exists ( select * from tbl_LST_PartnerListItems pli with (nolock) where pli.listId = and pli.partnerId = u.clientOperatorId ) and st.cho_id is null 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 getDate() > m.affiliationDate and ( m.deaffiliationDate is null or m.deaffiliationDate < m.affiliationDate ) and m.memberGroupChoId in ( ) ) and exists ( select * from buyingFilter bf where bf.cho_Id = u.operatorChoId ) ) select count(*) over() as fseRowCount, * insert into #_currentSelectionsTable# ( cho_id, clientOperatorId ) select result.operatorChoId, result.clientOperatorId delete st from result left outerinner join #_currentSelectionsTable# st with (nolock) on st.cho_id = result.operatorChoId where whitespace = 'Y' and st.cho_id is null and 1 = 1 and operatorTerritoryId in ( ) and ( 1 = 2 or operatorTerritoryPath like ) and ( 1 = 2 or operatorSegment like ) and primaryParentDstId primaryDstId = and 1 = 2 and #applyFilter( attributes.datastore.filter )# #_orderBy# offset #attributes.dataStore.skip# rows fetch next #attributes.dataStore.take# rows only for json path, include_null_values drop table if exists #_operatorsWithPurchasingTable#;