operatorDetails log {ts '2025-03-03 09:47:44'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534213 drop table if exists [tmp_memberGroups_BRO103_1741013264703]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013264703] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013264703] add constraint [pk_tmp_memberGroups_BRO103_1741013264703] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013264703] on [tmp_memberGroups_BRO103_1741013264703] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013264703]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013264703] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013264703] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013264703] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013264703] add constraint [pk_tmp_affiliations_BRO103_1741013264703] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013264703]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013264703] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013264703] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013264703] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013264703] add constraint [pk_tmp_memberships_BRO103_1741013264703] primary key ( operatorId ) {ts '2025-03-03 09:47:44'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534213 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013264703] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:47:50'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534215 drop table if exists [tmp_memberGroups_BRO103_1741013270333]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013270333] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013270333] add constraint [pk_tmp_memberGroups_BRO103_1741013270333] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013270333] on [tmp_memberGroups_BRO103_1741013270333] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013270333]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013270333] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013270333] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013270333] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013270333] add constraint [pk_tmp_affiliations_BRO103_1741013270333] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013270333]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013270333] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013270333] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013270333] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013270333] add constraint [pk_tmp_memberships_BRO103_1741013270333] primary key ( operatorId ) {ts '2025-03-03 09:47:50'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534215 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013270333] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:47:55'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534216 drop table if exists [tmp_memberGroups_BRO103_1741013275771]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013275771] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013275771] add constraint [pk_tmp_memberGroups_BRO103_1741013275771] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013275771] on [tmp_memberGroups_BRO103_1741013275771] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013275771]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013275771] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013275771] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013275771] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013275771] add constraint [pk_tmp_affiliations_BRO103_1741013275771] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013275771]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013275771] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013275771] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013275771] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013275771] add constraint [pk_tmp_memberships_BRO103_1741013275771] primary key ( operatorId ) {ts '2025-03-03 09:47:56'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534216 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013275771] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:47:57'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534218 drop table if exists [tmp_memberGroups_BRO103_1741013277255]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013277255] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013277255] add constraint [pk_tmp_memberGroups_BRO103_1741013277255] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013277255] on [tmp_memberGroups_BRO103_1741013277255] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013277255]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013277255] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013277255] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013277255] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013277255] add constraint [pk_tmp_affiliations_BRO103_1741013277255] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013277255]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013277255] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013277255] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013277255] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013277255] add constraint [pk_tmp_memberships_BRO103_1741013277255] primary key ( operatorId ) {ts '2025-03-03 09:47:57'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534218 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013277255] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:47:59'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534216 drop table if exists [tmp_memberGroups_BRO103_1741013279456]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013279456] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013279456] add constraint [pk_tmp_memberGroups_BRO103_1741013279456] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013279456] on [tmp_memberGroups_BRO103_1741013279456] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013279456]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013279456] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013279456] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013279456] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013279456] add constraint [pk_tmp_affiliations_BRO103_1741013279456] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013279456]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013279456] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013279456] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013279456] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013279456] add constraint [pk_tmp_memberships_BRO103_1741013279456] primary key ( operatorId ) {ts '2025-03-03 09:47:59'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534216 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013279456] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:56:52'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534218 drop table if exists [tmp_memberGroups_BRO103_1741013811933]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013811933] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013811933] add constraint [pk_tmp_memberGroups_BRO103_1741013811933] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013811933] on [tmp_memberGroups_BRO103_1741013811933] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013811933]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013811933] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013811933] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013811933] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013811933] add constraint [pk_tmp_affiliations_BRO103_1741013811933] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013811933]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013811933] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013811933] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013811933] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013811933] add constraint [pk_tmp_memberships_BRO103_1741013811933] primary key ( operatorId ) {ts '2025-03-03 09:56:52'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534218 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013811933] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:56:54'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534229 drop table if exists [tmp_memberGroups_BRO103_1741013814625]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013814625] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013814625] add constraint [pk_tmp_memberGroups_BRO103_1741013814625] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013814625] on [tmp_memberGroups_BRO103_1741013814625] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013814625]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013814625] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013814625] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013814625] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013814625] add constraint [pk_tmp_affiliations_BRO103_1741013814625] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013814625]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013814625] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013814625] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013814625] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013814625] add constraint [pk_tmp_memberships_BRO103_1741013814625] primary key ( operatorId ) {ts '2025-03-03 09:56:54'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534229 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013814625] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:56:57'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534220 drop table if exists [tmp_memberGroups_BRO103_1741013817233]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013817233] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013817233] add constraint [pk_tmp_memberGroups_BRO103_1741013817233] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013817233] on [tmp_memberGroups_BRO103_1741013817233] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013817233]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013817233] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013817233] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013817233] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013817233] add constraint [pk_tmp_affiliations_BRO103_1741013817233] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013817233]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013817233] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013817233] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013817233] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013817233] add constraint [pk_tmp_memberships_BRO103_1741013817233] primary key ( operatorId ) {ts '2025-03-03 09:56:57'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534220 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013817233] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:56:58'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534222 drop table if exists [tmp_memberGroups_BRO103_1741013818814]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013818814] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013818814] add constraint [pk_tmp_memberGroups_BRO103_1741013818814] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013818814] on [tmp_memberGroups_BRO103_1741013818814] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013818814]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013818814] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013818814] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013818814] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013818814] add constraint [pk_tmp_affiliations_BRO103_1741013818814] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013818814]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013818814] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013818814] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013818814] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013818814] add constraint [pk_tmp_memberships_BRO103_1741013818814] primary key ( operatorId ) {ts '2025-03-03 09:56:59'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534222 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013818814] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:57:00'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534502 drop table if exists [tmp_memberGroups_BRO103_1741013820054]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013820054] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013820054] add constraint [pk_tmp_memberGroups_BRO103_1741013820054] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013820054] on [tmp_memberGroups_BRO103_1741013820054] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013820054]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013820054] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013820054] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013820054] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013820054] add constraint [pk_tmp_affiliations_BRO103_1741013820054] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013820054]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013820054] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013820054] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013820054] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013820054] add constraint [pk_tmp_memberships_BRO103_1741013820054] primary key ( operatorId ) {ts '2025-03-03 09:57:00'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534502 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013820054] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER {ts '2025-03-03 09:57:01'} SQL declare @ownerId int = 103 ; declare @ownerType varchar(3) = 'BRO' ; declare @operatorId int = 9534222 drop table if exists [tmp_memberGroups_BRO103_1741013821472]; select opr.ownerId, opr.fsltablecode, cho.cho_type as oprCompanyType, opr.companyName, cho.cho_id as fsl_choId, opr.operatorId, row_number() over ( partition by opr.fsl_choId order by operatorId ) as mappingPriority into [tmp_memberGroups_BRO103_1741013821472] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and cho.cho_type in ( 'C', 'G' ); alter table [tmp_memberGroups_BRO103_1741013821472] add constraint [pk_tmp_memberGroups_BRO103_1741013821472] primary key ( fsltablecode, ownerId, operatorId ); create index [idx_tmp_memberGroups_BRO103_1741013821472] on [tmp_memberGroups_BRO103_1741013821472] (fsl_choId); drop table if exists [tmp_affiliations_BRO103_1741013821472]; with rawAffiliations as ( select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, affiliationStatus, partnerAffiliateNbr from tbl_CRM_Affiliations with (nolock) where affiliationStatus = 'A' and ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' union all select ownerId, ownerType, partnerType, partnerId, orgId, orgType, affiliationDate, 'A' affiliationStatus, null as partnerAffiliateNbr from tbl_CRM_InferredAffiliations with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerId = @operatorId and partnerType = 'OPR' ) , affiliations as ( select *, row_number() over ( partition by partnerType, partnerId, orgType, orgId order by affiliationDate ) as instanceCount from rawAffiliations ) , memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013821472] ) , affiliations2 as ( select a.*, row_number() over ( partition by a.partnerId, org.oprCompanyType order by a.affiliationDate ) as ranking, org.companyName, org.fsl_choId, org.oprCompanyType from affiliations a inner join memberGroups org on org.operatorId = a.orgId and org.ownerId = a.ownerId and org.fsltablecode = a.ownerType where instanceCount = 1 ) -- select a.partnerId operatorId, org.companyName, org.fsl_choId, a.affiliationDate, a.partnerAffiliateNbr, row_number() over ( partition by a.partnerId order by a.affiliationDate ) as ranking select a.ownerId, a.ownerType, a.partnerId as operatorId, companyName, fsl_choId, affiliationDate, partnerAffiliateNbr, ranking, case when oprCompanyType = 'G' then 'gpo' else 'cmc' end + cast( ranking as varchar ) as memberGroupN into [tmp_affiliations_BRO103_1741013821472] from affiliations2 a; alter table [tmp_affiliations_BRO103_1741013821472] alter column fsl_choId int not null; alter table [tmp_affiliations_BRO103_1741013821472] add constraint [pk_tmp_affiliations_BRO103_1741013821472] primary key ( ownerType, ownerId, operatorId, fsl_choId ); drop table if exists [tmp_memberships_BRO103_1741013821472]; with memberGroups as ( select * from [tmp_memberGroups_BRO103_1741013821472] with (nolock) ) , affiliations as ( select * from [tmp_affiliations_BRO103_1741013821472] with (nolock) ) , allMemberships as ( select p.operatorId, gpo1OPR.operatorId as gpoId_1, gpo2OPR.operatorId as gpoId_2, gpo3OPR.operatorId as gpoId_3, gpo4OPR.operatorId as gpoId_4, gpo1OPR.companyName as gpoName_1, gpo2OPR.companyName as gpoName_2, gpo3OPR.companyName as gpoName_3, gpo4OPR.companyName as gpoName_4, cmc1OPR.operatorId as cmcId_1, cmc2OPR.operatorId as cmcId_2, cmc3OPR.operatorId as cmcId_3, cmc4OPR.operatorId as cmcId_4, cmc1OPR.companyName as cmcName_1, cmc2OPR.companyname as cmcName_2, cmc3OPR.companyName as cmcName_3, cmc4OPR.companyName as cmcName_4, gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 from ( select operatorId, fsl_choId, memberGroupN from affiliations where ranking <= 4 ) as gpoData pivot ( min( fsl_choId ) for memberGroupN in ( gpo1, gpo2, gpo3, gpo4, cmc1, cmc2, cmc3, cmc4 ) ) as p left outer join memberGroups gpo1OPR with (nolock) on gpo1OPR.fsl_choId = p.gpo1 and gpo1OPR.mappingPriority = 1 left outer join memberGroups gpo2OPR with (nolock) on gpo2OPR.fsl_choId = p.gpo2 and gpo2OPR.mappingPriority = 1 left outer join memberGroups gpo3OPR with (nolock) on gpo3OPR.fsl_choId = p.gpo3 and gpo3OPR.mappingPriority = 1 left outer join memberGroups gpo4OPR with (nolock) on gpo4OPR.fsl_choId = p.gpo4 and gpo4OPR.mappingPriority = 1 left outer join memberGroups cmc1OPR with (nolock) on cmc1OPR.fsl_choid = p.cmc1 and cmc1OPR.mappingPriority = 1 left outer join memberGroups cmc2OPR with (nolock) on cmc2OPR.fsl_choid = p.cmc2 and cmc2OPR.mappingPriority = 1 left outer join memberGroups cmc3OPR with (nolock) on cmc3OPR.fsl_choid = p.cmc3 and cmc3OPR.mappingPriority = 1 left outer join memberGroups cmc4OPR with (nolock) on cmc4OPR.fsl_choid = p.cmc4 and cmc4OPR.mappingPriority = 1 ) -- select * from allMemberships select a.*, ga1OPR.AffiliationDate gpoAffiliationDate_1, ga1OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_1, ga2OPR.AffiliationDate gpoAffiliationDate_2, ga2OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_2, ga3OPR.AffiliationDate gpoAffiliationDate_3, ga3OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_3, ga4OPR.AffiliationDate gpoAffiliationDate_4, ga4OPR.partnerAffiliateNbr gpoPartnerAffiliateNbr_4, ca1OPR.AffiliationDate cmcAffiliationDate_1, ca1OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_1, ca2OPR.AffiliationDate cmcAffiliationDate_2, ca2OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_2, ca3OPR.AffiliationDate cmcAffiliationDate_3, ca3OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_3, ca4OPR.AffiliationDate cmcAffiliationDate_4, ca4OPR.partnerAffiliateNbr cmcPartnerAffiliateNbr_4 into [tmp_memberships_BRO103_1741013821472] from allMemberships a left outer join affiliations ga1OPR with (nolock) on ga1OPR.fsl_choId = a.gpo1 and ga1OPR.operatorId = a.operatorId left outer join affiliations ga2OPR with (nolock) on ga2OPR.fsl_choId = a.gpo2 and ga2OPR.operatorId = a.operatorId left outer join affiliations ga3OPR with (nolock) on ga3OPR.fsl_choId = a.gpo3 and ga3OPR.operatorId = a.operatorId left outer join affiliations ga4OPR with (nolock) on ga4OPR.fsl_choId = a.gpo4 and ga4OPR.operatorId = a.operatorId left outer join affiliations ca1OPR with (nolock) on ca1OPR.fsl_choid = a.cmc1 and ca1OPR.operatorId = a.operatorId left outer join affiliations ca2OPR with (nolock) on ca2OPR.fsl_choid = a.cmc2 and ca2OPR.operatorId = a.operatorId left outer join affiliations ca3OPR with (nolock) on ca3OPR.fsl_choid = a.cmc3 and ca3OPR.operatorId = a.operatorId left outer join affiliations ca4OPR with (nolock) on ca4OPR.fsl_choid = a.cmc4 and ca4OPR.operatorId = a.operatorId; alter table [tmp_memberships_BRO103_1741013821472] add constraint [pk_tmp_memberships_BRO103_1741013821472] primary key ( operatorId ) {ts '2025-03-03 09:57:01'} SQL declare @ownerId int = 103 ; declare @ownertype varchar(3) = 'BRO' ; declare @operatorId int = 9534222 ; declare @thisYear int = Year( getDate() ); declare @lastYear int = @thisYear - 1; declare @nextYear int = @thisYear + 1; with distributionDetailsRaw as ( select dd.ownerId, dd.ownerType, dd.operatorId, dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, nullif( dd.salesRepId, 0 ) as salesRepId, row_number() over ( partition by dd.cdr_recordId order by dd.distributionDetailsId ) as cdr_recordId_rank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), /* distributionDetails as ( select dd.distributionDetailsId, dd.cdr_recordId, cdr.cdr_dstName, dd.accountNumber, dd.avgWeeklyPurchases, dd.oprPriority, sr.fspro_userId as salesRepId, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary, case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 2 else 3 end as sortRank from tbl_OPR_DistributionDetails dd with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = dd.ownerId and cdr.fsl_tablecode = dd.ownerType and cdr.cdr_recordId = dd.cdr_recordId inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where dd.ownerId = @ownerId and dd.ownerType = @ownerType and dd.operatorId = @operatorId ), */ distributionDetails as ( select dd.*, sr.firstName salesRepFirstName, sr.lastName salesRepLastName, sr.email salesRepEmail /*, cast( case when dd.cdr_recordId = nullif( opr.distrib1, 0 ) then 1 else 0 end as bit ) as isPrimary, cast( case when dd.cdr_recordId = nullif( opr.distrib2, 0 ) then 1 else 0 end as bit ) as isSecondary */ from distributionDetailsRaw dd with (nolock) /* inner join tbl_OPR_ClientOperators opr ( nolock) on opr.ownerId = dd.ownerId and opr.fsltablecode = dd.ownerType and opr.operatorId = dd.operatorId */ left outer join tbl_FSPro_members sr with (nolock) on sr.ownerId = dd.ownerId and sr.fsl_tablecode = dd.ownerType and sr.fspro_userId = dd.salesRepId where cdr_recordId_rank = 1 ), partnerURLs as ( select p.partnerId, p.partnerType, p.instagramURL, p.linkedinURL, p.tiktokURL, p.facebookURL, p.youtubeURL, p.twitterURL from ( select urlType + 'URL' as urlType, urlValue, partnerId, partnerType from tbl_CRM_PartnerURLs with (nolock) where ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId and nullif( urlValue, '' ) is not null ) urls pivot ( min( urlValue ) for urlType in ( instagramURL, linkedinURL, tiktokURL, facebookURL, youtubeURL, twitterURL ) ) as p ), rawUnitForecast as ( select ownerId, ownerType, partnerId as operatorId, coalesce( unit_qty, 1 ) as num_units, case when cast( forecast_year as int ) = @thisYear then 'num_units_thisYr' when cast( forecast_year as int ) = @lastYear then 'num_units_lastYr' else 'num_units_nextYr' end as fieldName from tbl_MFR_TPF_PartnerUnitForecast with (nolock) where forecast_year in ( @lastYear, @thisYear, @nextYear ) and ownerId = @ownerId and ownerType = @ownerType and partnerType = 'OPR' and partnerId = @operatorId ), unitForecastPivot as ( select ownerId, ownerType, operatorId, num_units_lastYr, num_units_thisYr, num_units_nextYr from ( select * from rawUnitForecast with (nolock) ) as pivotSrc pivot ( max( num_units ) for fieldName in ( num_units_lastYr, num_units_thisYr, num_units_nextYr ) ) as pivotResult ), unitForecast as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, coalesce( uf.num_units_lastYr, coalesce( opr.num_units_lastYr, 1 ) ) num_units_lastYr, coalesce( uf.num_units_thisYr, coalesce( opr.num_units_thisYr, 1 ) ) num_units_thisYr, coalesce( uf.num_units_nextYr, coalesce( opr.num_units_nextYr, 1 ) ) num_units_nextYr from tbl_OPR_ClientOperators opr with (nolock) left outer join unitForecastPivot uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownerType = opr.fsltablecode and uf.operatorId = opr.operatorId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ), affiliations as ( select * from [tmp_memberships_BRO103_1741013821472] with (nolock) ), data as ( select opr.ownerId, opr.fsltablecode as ownerType, opr.operatorId, opr.oprType as priority, opr.classificationId as classificationId, opr.mfr_bsr_id, opr.profileComplete, case when opr.profileComplete = 'Y' then null else nullif( opr.profileErrors, '' ) end profileErrors, cast( floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as bigint ) as operatorTk, opr.companyName, opr.address, opr.address2, opr.city, opr.state, opr.zipCode, opr.countryId, opr.county, opr.phone, opr.phoneExt, opr.faxNumber, nullif( opr.mfrCustNum, '' ) mfrCustNum, nullif( opr.url, '' ) url, cho.cho_id, cho.cho_name, cho.cho_type, case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 ) then 'INDEPENDENT' when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 'UNIT' when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 'CHAIN HQ' when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 'GPO' when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 'CMC' when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 'SERVICE' when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 'HOLDING' else 'UNKNOWN' end as operatorType, cast( case when cho.cho_type = 'I' or ( opr.oprCompanyType = '0' and opr.independentYN = 1 )then 1 else 0 end as bit ) isIndependent, cast( case when cho.cho_type = 'U' or ( opr.oprCompanyType = '0' and opr.independentYN = 0 ) then 1 else 0 end as bit ) isUnit, cast( case when cho.cho_type = 'O' or opr.oprCompanyType = '1' then 1 else 0 end as bit ) isChainHQ, cast( case when cho.cho_type = 'G' or opr.oprCompanyType = 'G' then 1 else 0 end as bit ) isGPO, cast( case when cho.cho_type = 'C' or opr.oprCompanyType = 'C' then 1 else 0 end as bit ) isCMC, cast( case when cho.cho_type = 'S' or opr.oprCompanyType = 'S' then 1 else 0 end as bit ) isService, cast( case when cho.cho_type = 'H' or opr.oprCompanyType = 'H' then 1 else 0 end as bit ) isHolding, cast( case when cho.cho_id is not null then 1 else 0 end as bit ) is1FSMapped, nullif( opr.primaryContactId, 0 ) as primaryContactId, nullif( opr.cuisineId, 0 ) as cuisineId, cuisine.attrDescription as cuisine, cast( case when opr.tradeEnabled = 'Y' then 1 else 0 end as bit ) as tradeEnabled, nullif( opr.tradeNbr, '' ) tradeNbr, coalesce( opr.alcoholStatus, '?' ) as alcoholStatus, opr.seasonOpenDate, opr.seasonCloseDate, opr.seasonType, opr.directCustomer, opr.directCustomerNbr, nullif( opr.comments, '' ) comments, nullif( opr.comments2, '' ) comments2, nullif( opr.distrib1, 0 ) as distrib1, dd1.salesRepId as distrib1_salesRepId, dd1.accountNumber as distrib1_accountNumber, dd1.avgWeeklyPurchases as distrib1_avgWeeklyPurchases, nullif( opr.distrib2, 0 ) as distrib2, dd2.salesRepId as distrib2_salesRepId, dd2.accountNumber as distrib2_accountNumber, dd2.avgWeeklyPurchases as distrib2_avgWeeklyPurchases, -- json_query(( select * from distributionDetails as dd with (nolock) order by dd.cdr_dstName for json auto, include_null_values )) as distributionDetails, nullif( opr.chain_distr_notes, '' ) as chain_distr_notes, opr.recordSource, opr.dateCreated, opr.lastUpdated, opr.lastInteractionDate, nullif( opr.email, '' ) email, pu.instagramURL, pu.linkedinURL, pu.tiktokURL, pu.facebookURL, pu.youtubeURL, pu.twitterURL, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( tm.email, '' ) as territoryManagerEmail, opr.oprSegment, seg.clientSegId, seg.segmentName, seg.parentSegmentName, seg.segmentPath, opr.oprCompanyType, opr.unitsFollowSegment, opr.unitsFollowCuisine, opr.unitsFollowSeasonality, opr.unitsFollowGPO, opr.unitsFollowCMC, coalesce( popr.unitsFollowSegment, 'N' ) as segmentFollowsHQ, coalesce( popr.unitsFollowCuisine, 'N' ) as cuisineFollowsHQ, coalesce( popr.unitsFollowSeasonality, 'N' ) as seasonalityFollowsHQ, opr.oprParentCo, popr.operatorId parentOperatorId, popr.companyname parentCompanyName, opr.chainHQUnitNotes, opr.unitNbr, popr.chainHQUnitNotes as chainHQUnitNotesFromParent, nullif( opr.mdmType, '' ) mdmType, nullif( opr.mdmId, '' ) mdmId, opr.mdmDate, nullif( opr.relationshipRank, 0 ) relationshipRank, uf.num_units_lastYr as num_units_lastYr, uf.num_units_thisYr as num_units_thisYr, coalesce( uf.num_units_nextYr, uf.num_units_thisYr ) as num_units_nextYr, cast( case when exists( select * from tbl_OPR_ClientOperators u with (nolock) where u.ownerId = opr.ownerId and u.fsltablecode = opr.fsltablecode and u.oprParentCo = opr.operatorId ) then 1 else 0 end as bit ) hasUnits , a.cmcID_1, a.cmcPartnerAffiliateNbr_1, a.cmcAffiliationDate_1 , a.cmcID_2, a.cmcPartnerAffiliateNbr_2, a.cmcAffiliationDate_2 , a.cmcID_3, a.cmcPartnerAffiliateNbr_3, a.cmcAffiliationDate_3 , a.cmcID_4, a.cmcPartnerAffiliateNbr_4, a.cmcAffiliationDate_4 , a.gpoID_1, a.gpoPartnerAffiliateNbr_1, a.gpoAffiliationDate_1 , a.gpoID_2, a.gpoPartnerAffiliateNbr_2, a.gpoAffiliationDate_2 , a.gpoID_3, a.gpoPartnerAffiliateNbr_3, a.gpoAffiliationDate_3 , a.gpoID_4, a.gpoPartnerAffiliateNbr_4, a.gpoAffiliationDate_4 , bsr.email accountOwnerEmail, bsr.firstName accountOwnerFirstName, bsr.lastName accountOwnerLastName, bsr.title accountOwnerTitle , bsr.firstName + ' ' + bsr.lastName as accountOwnerFullName , opr.crmActive , cho.tastewiseId from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.operatorId = opr.oprParentCo and popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode= opr.fsltablecode and ter.territoryId = opr.territoryId left outer join tbl_OPR_ClientSegments seg with (nolock) on seg.ownerId = opr.ownerId and seg.fsl_tablecode = opr.fsltablecode and seg.clientSegId = opr.oprSegment left outer join tbl_ORG_Attributes cuisine with (nolock) on cuisine.ownerId = opr.ownerId and cuisine.fsl_tablecode = opr.fsltablecode and cuisine.attrId = opr.cuisineId left outer join tbl_Fspro_members bsr with (nolock) on bsr.fspro_userId = opr.mfr_bsr_id and bsr.ownerId > 0 left outer join tbl_Fspro_members tm with (nolock) on tm.fspro_userId = ter.primaryManager and tm.ownerId > 0 left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId left outer join partnerURLs pu with (nolock) on 1 = 1 left outer join unitForecast uf with (nolock) on uf.ownerId = opr.ownerId and uf.ownertype = opr.fsltablecode and uf.operatorId = opr.operatorId left outer join affiliations a with (nolock) on a.operatorId = opr.operatorId left outer join distributionDetails dd1 with (nolock) on dd1.cdr_recordId = opr.distrib1 left outer join distributionDetails dd2 with (nolock) on dd2.cdr_recordId = opr.distrib2 where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.operatorId = @operatorId ) , data_mappedData as ( select * from data ) , data_preData as ( select * from data_mappedData with (nolock) where ( 1 = 1 ) ) , data_data as ( select * from data_preData ) , data_return as ( select * from data_data where 1 = 1 order by operatorId offset 0 rows fetch next 10000000 rows only ) , data_result as ( select ( select count(*) from data_data ) as totalCount, ( select * from data_return for json auto, include_null_values ) as data ) select * from data_result for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER