select top 1 batchId, batchTime from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and fileFormat = '$NXT_OPERATOR_EXPORT_CSV' and batchTime < and status = 'OK' order by batchTime desc drop table if exists [#_tempFlexFieldTable#]; with flexValues as ( SELECT f.name as fieldName, fvv.data as fieldValue, fv.partnerId as operatorId FROM tbl_OPR_Fields f with (nolock) inner JOIN ( SELECT fieldId,partnerId, MAX(valueId) AS valueId FROM tbl_OPR_FieldValues fv GROUP BY fieldId,partnerId ) fv ON fv.fieldId = f.fieldId inner join tbl_OPR_FieldValues fvv on fvv.fieldId = fv.fieldId and fvv.valueId = fv.valueId where f.name in ( 'FIREFLYID', 'BRIZOID' ) and f.ownerId = and f.ownerType = ) select p.operatorId, p.FIREFLYID, p.BRIZOID into [#_tempFlexFieldTable#] from flexValues pivot( max( fieldValue ) for fieldName in ( FIREFLYID, BRIZOID )) as p where operatorId is not null; alter table [#_tempFlexFieldTable#] alter column operatorId int not null; alter table [#_tempFlexFieldTable#] add constraint [pk_#_tempFlexFieldTable#] primary key (operatorId); drop table if exists [#_tempTable#]; with export as ( select co.operatorId as operator_id, companyName as company_name, address as company_address, address2 as company_address_2, city as company_city, state as company_state, zipCode as company_zipcode, county as company_county ,country as company_country, companyPhone as company_phone, url as company_website, companyEmail as company_email, divisionName as territory_division, regionName as territory_region,territoryName as territory_name, territoryId as territory_id, classification as classification_name, classification as classification_desc, case when co.priority = '*' then 'A+' else co.priority end as priority, parentSegmentName as segment_parent, segmentName as segment,cuisine as cuisine_name, distrib1 as primary_dist_id, distrib2 as secondary_dist_id, distrib1_cdr_dstName as primary_dist_name, distrib1_cdr_dstCode as primary_dist_code, distrib1_cdr_dstAddress1 as primary_dist_addr, distrib1_cdr_dstCity as primary_dist_city, distrib1_cdr_dstState as primary_dist_state, distrib1_cdr_dstZip as primary_dist_zip, distrib2_cdr_dstName as secondary_dist_name, distrib2_cdr_dstCode as secondary_dist_code, distrib2_cdr_dstAddress1 as secondary_dist_addr, distrib2_cdr_dstCity as secondary_dist_city, distrib2_cdr_dstState as secondary_dist_state, distrib2_cdr_dstZip as secondary_dist_zip, salesRepId as rep_id, salesRepFirstName as rep_first_name, salesRepLastName as rep_last_name, salesRepEmail as rep_email, salesRepTitle as rep_title, numUnitsLastYear as num_units_ly, numUnits as num_units_ty, numUnitsNextYear as num_units_ny, parentCompanyName as parent_company_name, ff.FIREFLYID as firefly_id, ff.BRIZOID as brizo_id ,case when cho.cho_type = 'I' then 'Independent' when cho.cho_type = 'U' then 'Chain Unit' when cho.cho_type = 'O' then 'Company HQ' when cho.cho_type = 'C' then 'CMC' when cho.cho_type = 'G' then 'GPO' when cho.cho_type = 'H' then 'Holding' else 'Independent' end as company_type from tbl_DW_CRMOperators_#qmd_batch.ownerType##qmd_batch.ownerId# co with (nolock) left outer join tbl_CHO_Operators cho with (nolock) on cho.cho_id = co.oneFSId left outer join [#_tempFlexFieldTable#] ff with (nolock) on ff.operatorId = co.operatorId ) select operator_id,company_name, company_address, company_address_2, company_city, company_state, company_zipcode, company_county, company_country, company_phone, company_website, company_email, territory_division, territory_region, territory_name, territory_id, classification_name, classification_desc, priority,segment_parent, segment, cuisine_name, primary_dist_id, primary_dist_name, primary_dist_code, primary_dist_addr, primary_dist_city, primary_dist_state, primary_dist_zip, secondary_dist_id, secondary_dist_name, secondary_dist_code, secondary_dist_addr, secondary_dist_city, secondary_dist_state, secondary_dist_zip, rep_id, rep_first_name, rep_last_name, rep_email, rep_title, num_units_ly, num_units_ty, num_units_ny, parent_company_name, firefly_id, brizo_id, company_type into [#_tempTable#] from export

Exported #lsnumberformat( _exportCount )# Operators

update tbl_EAI_inboundBatches set fileName = , ttlDays = 365 where batchId = and ownerId = and ownerType =