declare @ownerId as int; declare @ownerType as varchar(3); set @ownerId = ; set @ownerType = ; with mappedDistributors as ( select mcdr.cdr_recordId, mcdr.ownerId, mcdr.fsl_tablecode, mcdr.cdr_dstName, mcdr.cdr_dstCompanyType, mcdr.fsl_dstId, mcdr.cdr_dstAddress1, mcdr.cdr_dstCity, mcdr.cdr_dstState, mcdr.cdr_dstZip, mcdr.mfr_bsr_id, mcdr.cdr_dstPath, mcdr.cdr_territoryId, mcdr.cdr_dstCode, mcdr.classificationId, /* correct for bad mapping priority sequencing */ row_number() over( partition by mcdr.fsl_dstId order by mcdr.fsl_mapPriority ) as fsl_mapPriority from tbl_CDR_Distributors mcdr with (nolock) where mcdr.ownerId = @ownerId and mcdr.fsl_tablecode = @ownerType and mcdr.fsl_dstId > 0 ) , distributorsRaw as ( select cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_recordId sales_cdr_recordId, cdr.cdr_dstName sales_cdr_dstName, case when mcdr.cdr_recordId is not null then mcdr.cdr_recordId else cdr.cdr_recordId end as cdr_recordId, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstName else cdr.cdr_dstName end as cdr_dstName, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstAddress1 else cdr.cdr_dstAddress1 end as cdr_dstAddress1, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstCity else cdr.cdr_dstCity end as cdr_dstCity, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstState else cdr.cdr_dstState end as cdr_dstState, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstZip else cdr.cdr_dstZip end as cdr_dstZip, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstPath else cdr.cdr_dstPath end as cdr_dstPath, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstCode else cdr.cdr_dstCode end as cdr_dstCode, case when mcdr.cdr_recordId is not null then mcdr.cdr_dstCompanyType else cdr.cdr_dstCompanyType end as cdr_dstCompanyType, case when mcdr.cdr_recordId is not null then mcdr.cdr_territoryId else cdr.cdr_territoryId end as cdr_territoryId, case when mcdr.cdr_recordId is not null then mcdr.fsl_dstId else null end as fsl_dstId, case when mcdr.cdr_recordId is not null then mcdr.mfr_bsr_id else cdr.mfr_bsr_id end as mfr_bsr_id, case when mcdr.cdr_recordId is not null then mcdr.classificationId else cdr.classificationId end as classificationId from tbl_CDR_Distributors cdr with (nolock) left outer join mappedDistributors mcdr on mcdr.ownerId = cdr.ownerId and mcdr.fsl_tablecode = cdr.fsl_tablecode and mcdr.fsl_dstId = cdr.fsl_dstId and mcdr.fsl_mapPriority = 1 and mcdr.fsl_dstId > 0 where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ), bygRaw as ( select byg.uniqueId as bgId, byg.name as BgName, cdr.fsl_dstId, row_number() over ( partition by cdr.fsl_dstId order by afl.affiliationDate desc, byg.name ) as seq from tbl_BYG_BuyingGroups byg with (nolock) inner join tbl_CRM_Affiliations afl with (nolock) on afl.ownerId= 66 and afl.ownerType = 'MSC' and afl.orgType = 'BYG' and afl.partnerType = 'DST' and afl.affiliationStatus = 'A' and afl.orgId = byg.uniqueId inner join tbl_CDR_Distributors cdr with (nolock) on cdr.fsl_dstId = afl.partnerId where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ), byg as ( select * from bygRaw with (nolock) where seq = 1 ) , distributorsA as ( select distinct cdr.ownerId, cdr.fsl_tablecode, cdr.cdr_dstCompanyType, cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstAddress1, cdr.cdr_dstCity, cdr.cdr_dstState, cdr.cdr_dstZip, cdr.cdr_dstPath, cdr.cdr_dstCode, ter.territoryId, ter.territoryPath, cdr.fsl_dstId, cdr.classificationId, cdr.mfr_bsr_id as accountOwner_fspro_userId, byg.bgId as buyingGroupId, byg.bgName as buyingGroup from distributorsRaw cdr with (nolock) inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = cdr.ownerId and ter.fsl_tablecode = cdr.fsl_tablecode and ter.territoryId = cdr.cdr_territoryId left outer join byg with (nolock) on byg.fsl_dstId = cdr.fsl_dstId where cdr.cdr_dstCompanyType in ( 'B', 'P' ) ) , distributors as ( select * from distributorsA with (nolock) ) #forJSON( 'distributors', attributes.dataStore, 'cdr_dstPath' )#