with chainDistribution as ( select ocd.chainOperatorId, nullif( ocd.distributionPartnerId, 0 ) as distributionPartnerId, round( cast( ocd.systemPercentage / 100.0 as float ), 2 ) as systemPercentage, cdr.cdr_recordId, cdr.cdr_dstName, cdr.cdr_dstCompanyType, opr.operatorId, opr.companyName, ter.territoryId, ter.name territoryName from tbl_OPR_ClientChainDistribution ocd with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = ocd.ownerId and opr.fsltablecode = ocd.ownerType and opr.operatorId = ocd.chainOperatorId and opr.crmActive = 'Y' inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = ocd.ownerId and ter.fsl_tablecode = ocd.ownerType and ter.territoryId = ocd.territoryId left outer join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = ocd.distributionPartnerId and cdr.ownerId = ocd.ownerId and cdr.fsl_tablecode = ocd.ownerType and cdr.crmActive = 'Y' where ocd.ownerId = and ocd.ownerType = and ocd.chainOperatorId = ) #forJSON( 'chainDistribution', attributes.datastore, 'territoryName' )#