1fsOperators log
{ts '2025-05-06 12:36:29'} SQL
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 ( 14777,14793,14794,14795,14796,14797,14798 ))
          
             and left(c.cho_zipcode,3) in ( '206','197','205','198','207','216','208','212','219','204','203','209','199','200','215','214','211','218','217','210','202','088','187','086','188','082','191','176','174','195','079','184','076','073','189','173','180','087','196','181','083','081','084','185','178','078','085','072','193','179','186','182','075','071','089','070','194','183','190','077','175','080','074','104','109','108','114','119','111','100','101','112','105','117','110','102','113','103','107','116','118','115','106','017','050','013','016','069','042','051','019','021','068','040','022','024','025','038','030','046','065','059','028','010','018','035','039','036','033','062','048','034','027','049','057','012','011','053','043','015','014','044','061','026','041','066','047','058','060','037','045','020','067','054','056','023','064','029','052','031','032','063','126','137','121','133','147','143','123','128','131','127','132','138','122','142','144','135','130','134','141','148','120','146','145','125','139','136','140','129','149','124','152','153','172','157','165','160','169','156','158','161','154','177','162','166','150','155','159','163','167','151','170','171','168','164' )
        
    )

    


, data_mappedData as (

	select *
		
	  from data

)

, data_preData as (

	
	select *
	  from data_mappedData with (nolock)

	
	
	where 

	(
	

	(
	[cho_name]
							
					like '%pizza%' 	
	)
and 

	(
	[cho_countryId]
							= '27' 	
	)
and 

	(
	

	(
	[cho_type]
							= 'O' 	
	)
or 

	(
	[cho_type]
							= 'I' 	
	)
	
	)
	
	)



	)

	, data_data as (

		select * 
		        
		  from data_preData
		  

	)



	, data_return as (
		select * from data_data
		where 1 = 1


	order by cho_name
		
		
		 offset 0 rows fetch next 25 rows only
	
	)

	
	, data_result as (
		select ( select count(*) from data_data ) as totalCount, 
			   
		       ( select * from data_return for json auto, include_null_values ) as data
		)


	select * from data_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
-- 6868
-- MFR