declare @ownerId int = ; declare @ownerType varchar(3) = declare @orgId int = ; with members as ( select coalesce( nullif( opr.buyingDecisions, '' ), 'N' ) buyingDecisions, a.affiliationId, opr.operatorId, opr.companyName, opr.address, opr.city, opr.state, opr.zipCode, ter.territoryId, ter.name as territoryName, ter.territoryPath, nullif( a.partnerAffiliateNbr, '' ) partnerAffiliateNbr, a.affiliationDate, a.affiliationStatus, popr.companyName as parentCompanyName, popr.operatorId as parentOperatorId from tbl_CRM_Affiliations a with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = a.ownerId and opr.fsltablecode = a.ownerType and opr.operatorId = a.partnerId and opr.crmActive = 'Y' left outer join tbl_OPR_ClientOperators popr with (nolock) on popr.ownerId = opr.ownerId and popr.fsltablecode = opr.fsltablecode and popr.operatorId = opr.oprParentCo inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsL_tablecode = opr.fsltablecode and ter.territoryId = opr.territoryId where a.ownerId = @ownerId and a.ownerType = @ownerType and a.orgId = @orgId and a.orgType = 'OPR' and a.partnerType = 'OPR' ) #forJSON( 'members', attributes.datastore, 'territoryPath,companyName' )#