---> select distinct operator_id_owner from tbl_IMPORT_OperatorSalesSummary where eai_batchId = and ownerId = and ownerType = update tbl_IMPORT_OperatorSalesSummary set operator_id_owner = 'CBS' where eai_batchId = and ownerId = and ownerType = and nullif( operator_id, '' ) is not null and nullif( operator_name, '' ) is not null and nullif( operator_address1, '' ) is not null and nullif( operator_city, '' ) is not null and nullif( operator_state, '' ) is not null and nullif( operator_zipcode, '' ) is not null ---> select distinct item_code from tbl_IMPORT_OperatorSalesSummary where eai_batchId = and ownerId = and ownerType = and coalesce( item_code, '' ) is not null and fseSkuId is null update tbl_IMPORT_OperatorSalesSummary set fseSkuId = where eai_batchId = and ownerId = and ownerType = and item_code = and fseSkuId is null select skuId, sku, prodId from qmd_skuLookup where skuType='CA' update tbl_IMPORT_OperatorSalesSummary set fseSkuId = where eai_batchId = and ownerId = and ownerType = and item_code = and fseSkuId is null select distinct distributor_id from tbl_IMPORT_OperatorSalesSummary with (nolock) where nullif( distributor_id, '' ) is not null and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select distinct cdr.crmActive, cdr.cdr_recordId, cdr.cdr_territoryId, cdr.cdr_dstName from tbl_CDR_Distributors cdr with (nolock) inner join tbl_CDR_DstCodes cdrc with (nolock) on cdrc.cdr_recordId = cdr.cdr_recordId and cdrc.ownerid = cdr.ownerId and cdrc.ownerType = cdr.fsl_tablecode where cdr.cdr_dstcompanytype = 'B' and cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_dstCode = order by cdr.crmActive desc, cdr.cdr_recordId update tbl_IMPORT_OperatorSalesSummary set fseCdrRecordId = where distributor_id = and eai_batchId = and ownerId = and ownerType = and fseCdrRecordId is null select distinct opr.operatorId, oss.operator_id from tbl_IMPORT_OperatorSalesSummary oss with (nolock) inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = oss.ownerId and opr.fsltablecode = oss.ownerType and opr.mfrCustNum = oss.operator_id where oss.eai_batchId = and oss.ownerId = and oss.ownerType = and nullif( oss.operator_id_owner, '' ) is null and oss.fseOperatorId is null update tbl_IMPORT_OperatorSalesSummary set fseOperatorId = where operator_id = and nullif( operator_id_owner, '' ) is null and eai_batchId = and ownerId = and ownerType = and fseOperatorId is null ---> select distinct opr.operatorId, oss.operator_id, oss.operator_id_owner from tbl_IMPORT_OperatorSalesSummary oss with (nolock) inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = oss.ownerId and opr.fsltablecode = oss.ownerType and opr.mdmId = oss.operator_id and opr.mdmType = oss.operator_id_owner where oss.eai_batchId = and oss.ownerId = and oss.ownerType = and oss.operator_id_owner <> '' and oss.fseOperatorId is null update tbl_IMPORT_OperatorSalesSummary set fseOperatorId = where operator_id = and operator_id_owner = and eai_batchId = and ownerId = and ownerType = and fseOperatorId is null select distinct 'OPR' rec_type, case when nullif( operator_id_owner, '' ) is not null then operator_id else '' end as external_id, case when nullif( operator_id_owner, '' ) is not null then 'MDM' else '' end as external_id_type, case when nullif( operator_id_owner, '' ) is not null then operator_id_owner else '' end as external_id_owner, operator_name as company_name, operator_address1 as company_address1, operator_address2 as company_address2, operator_city as company_city, operator_state as company_state, operator_zipcode as company_zip, 'C' as company_priority, '?' as territory_id from tbl_IMPORT_OperatorSalesSummary i with (nolock) where eai_batchId = and nullif( operator_id, '' ) is not null and nullif( operator_name, '' ) is not null and nullif( operator_address1, '' ) is not null and nullif( operator_city, '' ) is not null and nullif( operator_state, '' ) is not null and nullif( operator_zipcode, '' ) is not null and fseOperatorId is null order by company_name insert into tbl_IMPORT_Operators ( eai_batchId, eai_lineNumber, ownerId, ownerType , #_c# ) values ( , , , , ) update tbl_EAI_InboundBatches set loadedColumns = , lineCount = where batchId = select distinct i.fseOperatorId, i.fseCdrRecordId, opr.distrib1, opr.distrib2 from tbl_IMPORT_OperatorSalesSummary i with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = i.fseOperatorId and opr.ownerId = i.ownerId and opr.fsltablecode = i.ownerType and ( nullif( opr.distrib1, 0 ) is null or nullif( opr.distrib2, 0 ) is null ) where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseCdrRecordId > 0 update tbl_OPR_ClientOperators set #_distribCol# = where ownerId = and fsltablecode = and operatorId = and nullif( #_distribCol#, 0 ) is null insert into tbl_OPR_DistributionDetails ( ownerId, ownerType, operatorId, cdr_recordId ) values ( , , , ) select distinct accounting_year, accounting_month from tbl_IMPORT_OperatorSalesSummary with (nolock) where eai_batchId = and ownerId = and ownerType = and fseSkuId > 0 and fseOperatorId > 0 order by accounting_year, accounting_month select opr.mdmId, opr.mdmType, opr.operatorId, opr.territoryId from tbl_OPR_ClientOperators opr with (nolock) inner join ( select mdmType, mdmId from ( select mdmType, mdmId, count(*) c from tbl_OPR_ClientOperators opr with (nolock) where ownerId = and fsltablecode = and nullif( mdmId, '' ) is not null group by mdmType, mdmId having count(*) > 1 ) x ) xx on xx.mdmId = opr.mdmId and xx.mdmType = opr.mdmType where opr.ownerId= and opr.fsltablecode = select fseOperatorId tPartnerId, 'OPR' tpartnerType, sum( quantity_cases ) cases, sum( quantity_lbs ) lbs, 0 as dollars, s.skuId, 'SKU' productLevel, s.sku, p.prodId, case when s.bgtCatOverride = 'Y' and s.bgtCatId is not null then s.bgtCatId else coalesce( p.bgtCatId, 0 ) end bgtCatId, coalesce( s.equivUnitsPerCase, 1 ) equivUnitsPerCase, opr.territoryId from tbl_IMPORT_OperatorSalesSummary oss with (nolock) inner join tbl_PRD_Skus s with (nolock) on s.skuId = oss.fseSkuId and s.ownerId = oss.ownerId and s.fsl_tablecode = oss.ownerType left outer join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = oss.fseOperatorId and opr.ownerId = oss.ownerId and opr.fsltablecode = oss.ownerType where oss.ownerId = and oss.ownerType = and oss.accounting_year = and oss.accounting_month = and oss.fseOperatorId > 0 and oss.eai_batchId = group by oss.fseOperatorId, s.skuId, s.sku, p.prodId, s.bgtCatOverride, s.bgtCatId, p.bgtCatId, s.equivUnitsPerCase, opr.territoryId select operatorId, territoryId from qmd_mdmOperatorRepeats where mdmId = and mdmType = update tbl_MFR_TPF_Detail#_tpfSuffix# set forecast_qty = , forecast_amt = , forecast_alt = , prodId = , bgtCategoryId = , lastUpdateSource = , lastUpdate = , productItemCode = , territoryId = , rebateContractId = 0 where mfr_id = and tpartnerType = and tpartnerId = and skuId = and productLevel = 'SKU' and forecast_status = 'C' and forecast_year = and forecast_month = insert into tbl_MFR_TPF_Detail#_tpfSuffix# ( mfr_id, tpartnerType, tpartnerId, skuId, productLevel, forecast_status, forecast_year, forecast_month, forecast_qty, forecast_amt, forecast_alt, prodId, bgtCategoryId, lastUpdateSource, lastUpdate, productItemCode, territoryId, rebateContractId ) values ( , , , , 'SKU', 'C', , , , , , , , , , , , 0 ) ---> select item_code, item_description, count(*) lineCount, min( eai_lineNumber ) firstLine, sum( coalesce( quantity_cases, 0 ) ) cases, sum( coalesce( quantity_lbs, 0 )) lbs from tbl_IMPORT_OperatorSalesSummary with (nolock) where fseSkuId is null and eai_batchId = and ownerId = and ownerType = group by item_code, item_description order by cases desc, item_code select distributor_id, distributor_name, count(*) lineCount, min( eai_lineNumber ) firstLine from tbl_IMPORT_OperatorSalesSummary with (nolock) where fseCdrRecordId is null and eai_batchId = and ownerId = and ownerType = group by distributor_id, distributor_name order by distributor_id select operator_id, operator_name, count(*) lineCount, min( eai_lineNumber ) firstLine, sum( coalesce( quantity_cases, 0 ) ) cases, sum( coalesce( quantity_lbs, 0 )) lbs from tbl_IMPORT_OperatorSalesSummary with (nolock) where fseOperatorId is null and eai_batchId = and ownerId = and ownerType = group by operator_id, operator_name order by cases desc, operator_name select accounting_year as year, accounting_month as month, sum( case when ( fseOperatorId is null or fseSkuId is null ) then 1 else 0 end ) linesNotLoaded, sum( case when ( fseOperatorId is null or fseSkuId is null ) then 0 else 1 end ) linesLoaded, count(*) totalLines from tbl_IMPORT_OperatorSalesSummary with (nolock) where eai_batchId = and ownerId = and ownerType = group by accounting_year, accounting_month order by accounting_year, accounting_month

Lines are not loaded when the SKU or Operator cannot be found or the Operator cannot be added (missing ID, missing address, etc...)

select i.accounting_year, i.accounting_month, p.shortDesc product, count( distinct fseOperatorId ) operators, round( sum( quantity_cases ), 0 ) as cases, round( sum( coalesce( quantity_lbs, 0 ) ), 0 ) as lbs from tbl_IMPORT_OperatorSalesSummary i with (nolock) inner join tbl_PRD_skus s with (nolock) on s.skuId = i.fseSkuId and s.ownerId = i.ownerId and s.fsl_tablecode = i.ownerType inner join tbl_PRD_Products p with (nolock) on p.prodId = s.prodId and p.ownerId = s.ownerId and p.fsl_tablecode = s.fsl_tablecode where i.eai_batchId = and i.ownerId = and i.ownerType = group by i.accounting_year, i.accounting_month, p.shortDesc order by i.accounting_year desc, i.accounting_month desc, product update tbl_EAI_inboundBatches set processingState = where batchId = and ownerId = and ownerType =