declare @ownerId int = ; declare @ownerType varchar(3) = ; with crmActivePartners as ( union all select opr.operatorId as partnerId, 'OPR' as partnerType, opr.companyName as partnerName, cho.cho_id as oneFsId, opr.territoryId , opr.city, opr.state, opr.zipcode , case when cho.cho_type = 'I' then 'INDEPENDENT' when cho.cho_type = 'U' then 'UNIT' when cho.cho_type = 'O' then 'CHAIN HQ' when cho.cho_type = 'G' then 'GPO' when cho.cho_type = 'C' then 'CMC' when cho.cho_type = 'S' then 'SERVICE' when cho.cho_type = 'H' then 'HOLDING' else 'UNKNOWN' end as businessType, opr.address from tbl_OPR_ClientOperators opr with (nolock) innerleft outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = opr.fsl_choId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.crmActive = 'Y' and opr.territoryId in ( ) select cdr.cdr_recordId as partnerId, 'CDR' as partnerType, cdr.cdr_dstName as partnerName, dst.dstId as oneFsId, cdr.cdr_territoryId territoryId, cdr.cdr_dstCity as city, cdr.cdr_dstState as state, cdr.cdr_dstZip as zipcode , case when dst.dstCompanyType = 'P' then 'PARENT' when dst.dstCompanyType = 'B' and cdr.cdr_specialAcctType is not null then 'SPECIAL' when dst.dstCompanyType = 'B' then 'BRANCH' else dst.dstCompanyType end as businessType , cdr.cdr_dstAddress1 address from tbl_CDR_Distributors cdr with (nolock) innerleft outer join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.crmActive = 'Y' and cdr.cdr_territoryId in ( ) ), formattedPartners as ( select cap.*, cap.partnerType + ':' + cast( cap.partnerId as varchar ) as partnerKey, t.name as territoryName, t.territoryPath, floor( 10191817 * sqrt( cap.partnerId * log10( cap.partnerId ))) as partnerTk from crmActivePartners cap inner join tbl_TER_Territories t with (nolock) on t.territoryId = cap.territoryId and t.ownerId = @ownerId and t.fsl_tablecode = @ownerType ) #forJSON( 'formattedPartners', attributes.dataStore, 'partnerName' )#