declare @ownerType varchar(3) = ; declare @ownerId int = ; declare @zip3d varchar(3) = ; with hasLocalMarketsRaw as ( select t.ownerId, t.fsl_tablecode, t.parentTerritoryId as territoryId, 1 as hasLocalMarkets from tbl_TER_Territories t with (nolock) inner join tbl_TER_TerritoryLevels l with (nolock) on l.levelId = t.levelId and l.ownerId = t.ownerId and l.fsl_tablecode = t.fsl_tablecode and l.brokerLevel = 1 where t.ownerId = @ownerId and t.fsl_tablecode = @ownerType union all select t.ownerId, t.fsl_tablecode, t.parentTerritoryId as territoryId, lm.hasLocalMarkets from tbl_TER_Territories t with (nolock) inner join hasLocalMarketsRaw lm on lm.territoryId = t.territoryId and lm.ownerId = t.ownerId and lm.fsl_tablecode = t.fsl_tablecode ), hasLocalMarkets as ( select distinct * from hasLocalMarketsRaw ), result as ( SELECT L.LevelID, TRIM(L.Name) as LvName, t.TerritoryID, TRIM(t.Name) as name, trim( t.name ) as territoryName, trim( nullif( t.sName, '' ) ) as shortTerritoryName, trim( l.name ) as levelName, t.territoryPath , t.managerChain, case when l.levelId is not null then 'Y' else 'N' end as assignable , l.brokerLevel as localMarket, coalesce( hlm.hasLocalMarkets, 0 ) as hasLocalMarkets , t.primaryManager, t.secondaryManager, t.internalSalesId , ( select distinct tm.teamMemberRole, tm.teamMemberUserId, f.firstName, f.lastName from tbl_TER_TeamMembers tm with (nolock) inner join tbl_Fspro_members f with (nolock) on f.ownerId = tm.ownerId and f.fsl_tablecode = tm.ownerType and f.fspro_userId = tm.teamMemberUserId where tm.ownerId = @ownerId and tm.ownertype = @ownerType and tm.territoryId = t.territoryId for json path, include_null_values ) as teamMembers FROM tbl_TER_Territories t with (nolock) inner join tbl_TER_TerritoryLevels L with (nolock) on t.LevelID = l.LevelID and t.ownerId = l.ownerId and t.fsl_tableCode = l.FSL_TableCode left outer join hasLocalMarkets hlm with (nolock) on hlm.ownerId = t.ownerId and hlm.fsl_tablecode = t.fsl_tablecode and hlm.territoryId = t.territoryId inner join tbl_TER_ZipCodeLink zcl with (nolock) on zcl.territoryId = t.territoryId inner join tbl_UT_ZipCodes_3D zc with (nolock) on zc.zip3d_id = zcl.zip3d_Id and zc.zipCode_3d = @zip3d WHERE t.OwnerID = @ownerId AND t.FSL_TableCode = @ownerType and t.territoryId in ( ) and t.territoryId in ( select ptr.resourceId from tbl_BRO_PrincipalTerritoryResources ptr with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId= ptr.ownerId and pt.ownerType = ptr.ownerType and pt.principalTerritoryId = ptr.principalTerritoryId inner join tbl_BRO_Principals p with (nolock) on p.ownerId = pt.ownerId and p.ownerType = pt.ownerType and p.principalId = pt.principalId where ptr.resourceType = 'TER' and ptr.ownerId = @ownerId and ptr.ownerType = @ownerType and p.mfrId = ) and t.territoryId in ( select tm.territoryId from tbl_TER_TeamMembers tm with (nolock) where tm.ownerId = @ownerId and tm.ownerType = @ownerType and tm.teamMemberUserId = and tm.teamMemberRole in ( ) ) ) #forJSON( 'result', attributes.dataStore, 'territoryPath' )#