select zipcode_3d as zipcode from tbl_TER_ZipCodeLink zcl with (nolock) inner join tbl_UT_ZipCodes_3D zc with (nolock) on zc.zip3d_id = zcl.zip3d_id inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = zcl.territoryId where zcl.territoryid in () and ter.fsl_tablecode = and ter.ownerId = select distinct dst.dstState from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId and dst.dstCompanyType = 'B' where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_territoryId in () and nullif( dst.dststate, '' ) is not null declare @ownerId int = ; declare @ownerType varchar(3) = ; with clientSegmentsRaw as ( select clientSegId, segmentId, row_number() over ( partition by segmentId order by segmentPath ) ranking from tbl_OPR_ClientSegments cs with (nolock) where cs.ownerId = @ownerId and cs.fsl_tablecode = @ownerType and segmentId > 0 ), clientSegments as ( select clientSegId, segmentId from clientSegmentsRaw with (nolock) where ranking = 1 ), data as ( select c.cho_id, c.cho_type, c.cho_name, c.cho_address, c.cho_city, c.cho_state, c.cho_zipCode, c.cho_countryId, c.cho_segmentId, nullif( c.cho_url, '' ) cho_url, nullif( c.cho_phone, '' ) cho_phone, o.operatorId, t.territoryId, t.name as territoryName, t.territoryPath, o.crmActive, case when o.operatorId is not null then o.oprSegment else cs.clientSegId end clientSegId from tbl_cho_operators c with (nolock) left outer join tbl_OPR_ClientOperators o with (nolock) on o.ownerId = @ownerId and o.fsltablecode = @ownerType and o.fsl_choId = c.cho_id and o.territoryId > 0 left outer join tbl_TER_Territories t with (nolock) on t.ownerId = o.ownerId and t.fsl_tablecode = o.fsltablecode and t.territoryId = o.territoryId left outer join clientSegments cs with (nolock) on cs.segmentId = c.cho_segmentId where coalesce(c.cho_acctclosed,'0') <> '1' and c.cho_name not like 'Insufficient%' and ( o.territoryId is null or o.territoryId in ( )) and c.cho_state in () and left(c.cho_zipcode,3) in () ) #forJSON( 'data', attributes.dataStore, 'cho_name' )#