declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @objectiveId int = ; with data as ( select stf.linkId, stf.quota, stf.quotaAchieved, case when stf.quota != 0 then coalesce( stf.quotaAchieved, 0 ) / cast( stf.quota as float ) else 0.0 end as quotaAchievement, stf.insertType, mem.fspro_userId, mem.firstName + ' ' + mem.lastName as fullName, mem.title, mem.email, ter.name as territoryName, ter.territoryId, ter.territoryPath, case when coalesce( stf.territoryId, 0 ) = coalesce( obj.territoryId, 0 ) and stf.territoryId > 0 then 'Y' else 'N' end objectiveTerritory, obj.objectiveId from tbl_bcrm_objectives obj with (nolock) INNER JOIN tbl_BCRM_ObjectiveStfLink stf with (nolock) on stf.objectiveid = obj.objectiveid and stf.ownerId = obj.ownerId and stf.ownerType = obj.ownerType left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = obj.ownerId and ter.fsl_tablecode = obj.ownerType and ter.territoryId = obj.territoryId inner join tbl_FSPro_Members mem with (nolock) on mem.ownerId = stf.ownerId and mem.fsl_tablecode= stf.ownerType and mem.fspro_userId = stf.fspro_userId WHERE obj.parentObjectiveId = @objectiveId and obj.ownerid = @ownerId and obj.ownertype = @ownerType ) #forJSON( 'data', attributes.datastore, 'territoryName,fullName' )#