select name as tableName from sys.tables where name like 'tbl_DW_OperatorAnalyzer_MFR%' and name not like '%MFR0' select batchId, batchTime from tbl_EAI_inboundBatches with (nolock) where ownerId = and ownerType = and status = 'OK' and batchTime >= and fileFormat = '$OperatorAnalyzerETL' select *, 'N' stepComplete from qmd_analyzerTables where batchId > 0 truncate table tbl_DW_OperatorAnalyzerSummary; truncate table tbl_DW_OperatorAnalyzerClientSummary;

Skipping #tableName# (demo account)

declare @currentMonth datetime = DateFromParts( Year( getDate() ), Month( getDate() ), 1 ); select top 1 dataId from [#tableName#] as oa with (nolock) where datediff( month, oa.purchasingMonth, @currentMonth ) < 12

Skipping #tableName# (no data)

Skipping for running batch

declare @currentMonth datetime; set @currentMonth = DateFromParts( Year( getDate() ), Month( getDate() ), 1 ); with x as ( select oa.ownerId, oa.ownerType, oa.cho_id, cho.cho_name as operatorName, oa.operatorSegment, oa.dstId, oa.dstName, oa.parentDstId, oa.parentDstName, sum( oa.forecast_qty * ph.unitPrice_USD ) forecast_amt, sum( oa.forecast_qty ) forecast_qty, min( oa.purchasingMonth ) firstMonth, max( oa.purchasingMonth ) lastMonth from #tableName# oa with (nolock) inner join tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) on ph.ownerId = oa.ownerId and ph.ownerType = oa.ownerType and ph.skuId = oa.skuId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = oa.cho_Id where oa.cho_type in ( 'I', 'O' ) and datediff( month, oa.purchasingMonth, @currentMonth ) < 12 and coalesce( oa.forecast_qty, 0 ) > 0 group by oa.ownerId, oa.ownerType, oa.cho_id, cho.cho_name, oa.operatorSegment, oa.dstId, oa.dstName, oa.parentDstId, oa.parentDstName ) insert into tbl_DW_OperatorAnalyzerSummary ( cho_id, operatorName, operatorSegment, ownerId, ownerType, firstMonth, lastMonth, forecast_qty, forecast_amt, dstId, dstName, parentDstId, parentDstName ) select cho_id, operatorName, operatorSegment, ownerId, ownerType, firstMonth, lastMonth, forecast_qty, forecast_amt, dstId, dstName, parentDstId, parentDstName from x; declare @currentMonth datetime; set @currentMonth = DateFromParts( Year( getDate() ), Month( getDate() ), 1 ); with x as ( select oa.ownerId, oa.ownerType, oa.cho_id, cho.cho_name as operatorName, oa.operatorSegment, sum( oa.forecast_qty * coalesce( ph.unitPrice_USD, 35.0 * case when ph.qtyUOM != 'CASES' then ph.qtyConversionToCases else 1.0 end) ) forecast_amt, sum( forecast_amt ) forecast_amt_raw, sum( oa.forecast_qty ) forecast_qty, min( oa.purchasingMonth ) firstMonth, max( oa.purchasingMonth ) lastMonth, count( distinct oa.purchasingMonth ) purchasingMonths, count( distinct oa.skuId ) skuCount, 1.0 equivalenceFactor, datediff( month, max( oa.purchasingMonth ), @currentMonth ) lagMonths from #tableName# oa with (nolock) inner join tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) on ph.ownerId = oa.ownerId and ph.ownerType = oa.ownerType and ph.skuId = oa.skuId inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = oa.cho_Id where oa.cho_type in ( 'I', 'O' ) and datediff( month, oa.purchasingMonth, @currentMonth ) < 12 and coalesce( oa.forecast_qty, 0 ) > 0 group by oa.ownerId, oa.ownerType, oa.cho_id, cho.cho_name, oa.operatorSegment ) insert into tbl_DW_OperatorAnalyzerClientSummary ( cho_id, operatorName, operatorSegment, ownerId, ownerType, firstMonth, lastMonth, forecast_qty, forecast_amt, forecast_amt_raw, purchasingMonths, skuCount, equivalenceFactor, lagMonths ) select cho_id, operatorName, operatorSegment, ownerId, ownerType, firstMonth, lastMonth, forecast_qty, forecast_amt, forecast_amt_raw, purchasingMonths, skuCount, equivalenceFactor, lagMonths from x;

Sleeping #now()#

drop table if exists [#_segmentFactorsTable#]; select operatorSegment, count( distinct ownerId ) as clientCount, avg( forecast_qty / forecast_amt ) as factor, avg( forecast_qty ) quantity, avg( forecast_amt ) dollars into [#_segmentFactorsTable#] from tbl_DW_OperatorAnalyzerClientSummary with (nolock) where operatorSegment is not null group by operatorSegment; alter table [#_segmentFactorsTable#] alter column operatorSegment varchar(200) not null; alter table [#_segmentFactorsTable#] add constraint PK_#_segmentFactorsTable# primary key ( operatorSegment ); truncate table tbl_DW_OperatorAnalyzerRollup insert into tbl_DW_OperatorAnalyzerRollup ( cho_id, operatorName, mfrs, totalQty, firstMonth, lastMonth, clientAvg, monthlyClientAvg ) select cho_id, operatorName, count( distinct ownerId ) mfrs, sum( forecast_qty ) totalQty, min( firstmonth ) firstMonth, max( lastMonth ) lastMonth, -- sum( forecast_amt ) / count( distinct ownerId ) clientAvg, -- sum( forecast_amt ) / max( purchasingMonths ) as clientAvg, sum( forecast_amt ) / sum( purchasingMonths ) as clientAvg, -- sum( forecast_amt / cast( datediff( month, firstMonth, lastMonth ) + 1 as float )) / count( distinct ownerId ) monthlyClientAvg -- sum( forecast_amt / purchasingMonths * 12.0 ) / count( distinct ownerId ) as monthlyClientAvg sum( forecast_amt ) / sum( purchasingMonths ) as monthlyClientAvg from tbl_DW_OperatorAnalyzerClientSummary with (nolock) where cho_id > 0 and operatorName not like '%blanket%' and operatorName not like '%insufficient%' group by cho_id, operatorName; with ranked as ( select row_number() over ( order by monthlyClientAvg desc ) masterRank, cho_id, operatorName, monthlyClientAvg from tbl_DW_OperatorAnalyzerRollup with (nolock) ), scored as ( select cho_id, masterRank, cast( cume_dist() over( order by monthlyClientAvg ) * 100.0 as integer) as percentScore from ranked ) update r set r.masterRank = s.masterRank, r.percentScore = s.percentScore from tbl_DW_OperatorAnalyzerRollup r inner join scored s on s.cho_id = r.cho_Id /* * SECONDARY DISTRIBUTOR */ with /* units and indendents are the single distributor with the greated volume */ locationDistributionDetail as ( select s.cho_id, s.dstId, s.dstName, s.parentDstId, s.parentDstName, sum( forecast_amt ) as forecast_amt from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = s.cho_id and cho.cho_type in ( 'U', 'I' ) group by s.cho_id, s.dstId, s.dstName, s.parentDstId, s.parentDstName ), locationDistributionRanked as ( select cho_id, dstId, dstName, parentDstId, parentDstName, ROW_NUMBER() OVER(PARTITION BY cho_id ORDER BY forecast_amt DESC) AS ranking from locationDistributionDetail ), locationDistribution as ( select * from locationDistributionRanked where ranking = 1 ), /* hq is special handling those that buy from from a single branch those that buy from a multiple branches of a single parent those that buy from muliple parents */ hqDistributionSummary as ( select s.cho_id, count( distinct s.parentDstId ) parents, sum( forecast_amt ) as forecast_amt, count( distinct s.dstId ) branches from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = s.cho_id and cho.cho_type in ( 'O' ) group by s.cho_id ), /* single parent/branch */ hqDistributionDetailEasy as ( select s.cho_id, s.dstId, s.dstName, s.parentDstId, s.parentDstName, sum( s.forecast_amt ) as forecast_amt from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join hqDistributionSummary hqs with (nolock) on hqs.cho_id = s.cho_id and hqs.branches = 1 and hqs.parents = 1 group by s.cho_id, s.dstId, s.dstName, s.parentDstId, s.parentDstName ), /* multple branches one parent */ hqDistributionDetailSingleParent as ( select s.cho_id, cast( null as int ) dstId, cast( null as varchar) dstName, s.parentDstId, s.parentDstName, sum( s.forecast_amt ) as forecast_amt from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join hqDistributionSummary hqs with (nolock) on hqs.cho_id = s.cho_id and hqs.branches > 1 and hqs.parents = 1 group by s.cho_id, s.parentDstId, s.parentDstName ), /* multiple parents, use the parent with the greateds volume */ hqDistributionDetailMultipleDetail as ( select s.cho_id, cast( null as int ) dstId, cast( null as varchar) dstName, s.parentDstId, s.parentDstName, sum( s.forecast_amt ) as forecast_amt from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join hqDistributionSummary hqs with (nolock) on hqs.cho_id = s.cho_id and hqs.parents > 1 group by s.cho_id, s.parentDstId, s.parentDstName ), hqDistributionDetailMultipleRanked as ( select cho_id, dstId, dstName, parentDstId, parentDstName, ROW_NUMBER() OVER(PARTITION BY cho_id ORDER BY forecast_amt DESC) AS ranking from hqDistributionDetailMultipleDetail ), hqDistributionDetailMultiple as ( select * from hqDistributionDetailMultipleRanked where ranking = 1 ), /* add them all together */ primaryDistribution as ( select * from hqDistributionDetailMultiple union all select * from hqDistributionDetailEasy union all select * from hqDistributionDetailSingleParent union all select * from locationDistribution ) /* update the summary table */ update r set r.primaryDstId = x.dstId, r.primaryDstName = x.dstName, r.primaryParentDstId = x.parentDstId, r.primaryParentDstName = x.parentDstName from tbl_DW_OperatorAnalyzerRollup r inner join primaryDistribution x on x.cho_id = r.cho_id; /* * SECONDARY DISTRIBUTOR */ with /* units and indendents are the single distributor with the greated volume */ locationDistributionDetail as ( select s.cho_id, s.dstId, s.dstName, s.parentDstId, s.parentDstName, sum( forecast_amt ) as forecast_amt from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = s.cho_id and cho.cho_type in ( 'U', 'I' ) group by s.cho_id, s.dstId, s.dstName, s.parentDstId, s.parentDstName ), locationDistributionRanked as ( select cho_id, dstId, dstName, parentDstId, parentDstName, ROW_NUMBER() OVER(PARTITION BY cho_id ORDER BY forecast_amt DESC) AS ranking from locationDistributionDetail ), locationDistribution as ( select * from locationDistributionRanked where ranking = 2 ), /* hq is special handling those that buy from muliple parents */ hqDistributionSummary as ( select s.cho_id, count( distinct s.parentDstId ) parents, sum( forecast_amt ) as forecast_amt, count( distinct s.dstId ) branches from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join tbl_CHO_Operators cho with (nolock) on cho.cho_id = s.cho_id and cho.cho_type in ( 'O' ) group by s.cho_id ), /* multiple parents, use the parent with the second greatest volume */ hqDistributionDetailMultipleDetail as ( select s.cho_id, cast( null as int ) dstId, cast( null as varchar) dstName, s.parentDstId, s.parentDstName, sum( s.forecast_amt ) as forecast_amt from tbl_DW_OperatorAnalyzerSummary s with (nolock) inner join hqDistributionSummary hqs with (nolock) on hqs.cho_id = s.cho_id and hqs.parents > 1 group by s.cho_id, s.parentDstId, s.parentDstName ), hqDistributionDetailMultipleRanked as ( select cho_id, dstId, dstName, parentDstId, parentDstName, ROW_NUMBER() OVER(PARTITION BY cho_id ORDER BY forecast_amt DESC) AS ranking from hqDistributionDetailMultipleDetail ), hqDistributionDetailMultiple as ( select * from hqDistributionDetailMultipleRanked where ranking = 2 ), /* add them all together */ secondaryDistribution as ( select * from hqDistributionDetailMultiple union all select * from locationDistribution ) update r set r.secondaryDstId = x.dstId, r.secondaryDstName = x.dstName, r.secondaryParentDstId = x.parentDstId, r.secondaryParentDstName = x.parentDstName from tbl_DW_OperatorAnalyzerRollup r inner join secondaryDistribution x on x.cho_id = r.cho_id; with x as ( select cho_id, coalesce( primaryDstId, primaryParentDstId ) opa_dstId1, coalesce( secondaryDstId, secondaryParentDstId ) opa_dstId2 from tbl_DW_OperatorAnalyzerRollup with (nolock) ) update cho set cho.cho_dstId1 = x.opa_dstId1, cho.cho_dstId2 = x.opa_dstId2 from x inner join tbl_CHO_Operators cho on cho.cho_id = x.cho_id where opa_dstId1 > 0 and ( opa_dstId1 != coalesce( cho.cho_dstId1, 0 ) or coalesce( opa_dstId2, 0 ) != coalesce( cho.cho_dstId2, 0 ))

Skipping for running batch

select c.parameterName, c.parameterValue from fsenablers..tbl_APP_Configurations c with (nolock) where c.ownerId = and c.ownerType = and c.applicationCode = 'CRM' and c.parameterName = 'tastewiseEnabled' and c.parameterValue = 'true'

#htmleditformat( tableName )# #batchId# #batchTime#

truncate table tbl_DW_1fsSources#_tableSuffix#; declare @ownerId as int declare @ownerType as char(3) set @ownerId = set @ownerType = ; with scope1fs as ( select distinct @ownerType ownerType, @ownerId ownerId, 'CHO' as [sourceType], cho.cho_id as [sourceId], cho.cho_name as [sourceName] from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_CHO_Operators cho with(nolock) on cho.cho_id = opr.fsl_choId inner join tbl_TPM_Claims c with (nolock) on c.partnerId = opr.operatorId and c.partnerType = 'OPR' and c.ownerId = opr.ownerId and c.ownerType = opr.fsltablecode where opr.fsl_choId > 0 and opr.ownerId = @ownerId and opr.fsltablecode = @ownerType union all select distinct @ownerType ownerType, @ownerId ownerId, 'DST' as [sourceType], coalesce( dstp.dstId, dst.dstId ) as [sourceId], case when dstp.dstId is not null then dstp.dstName else dst.dstName end as [sourceName] from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId left outer join tbl_DST_Distributors dstp with (nolock) on dstp.dstId = dst.dstParentCo inner join tbl_TPM_Claims c with (nolock) on c.partnerId = cdr.cdr_recordId and c.partnerType = 'CDR' and c.ownerId = cdr.ownerId and c.ownerType = cdr.fsl_tablecode where cdr.fsl_dstId > 0 and cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ) insert into tbl_DW_1fsSources#_tableSuffix# ( ownerType, ownerId, sourceType, sourceId, sourceName ) select ownerType, ownerId, sourceType, sourceId, sourceName from scope1fs where exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where opr.fsl_choId > 0 and opr.ownerId = scope1fs.sourceId and opr.fsltablecode = scope1fs.sourceType ) select * from tbl_DW_1fsSources#_tableSuffix# update tbl_EAI_inboundBatches set message = '#currentRow# of #recordCount# - tbl_DW_1fsUniverse#_tableSuffix#' where ownerId = and ownerType = and batchId = delete from tbl_DW_1fsUniverse#_tableSuffix# where ownerId = and ownerType = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @currentMonth datetime = DateFromParts( Year( getDate() ), Month( getDate() ), 1 ); declare @whitespaceCutoff datetime = dateadd( month, -12, @currentMonth ); with preSegments as ( select segment, charindex( '/', segment ) s1, charindex( '/', segment, charindex( '/', segment ) + 1 ) s2, segmentId from tbl_OPR_Segments with (nolock) ), segments as ( select substring( segment, 1, s1 - 1 ) channelSegment, subString( segment, s1 + 1, s2 - s1 - 1 ) mainSegment, substring( segment, s2 + 1, len( segment ) - s2 ) subSegment, substring( segment, s1 + 1, len( segment ) - s1 ) operatorSegment, segment as fullSegment, segmentId from preSegments ), territories as ( select z3d.zipCode_3d, ter.name as territoryName,ter.territoryId, ter.territoryPath, ter.regionName, ter.divisionName, row_number() over( partition by z3d.zipCode_3d order by ter.territoryId ) as territoryRank from tbl_UT_ZipCodes_3d z3d with (nolock) inner join tbl_TER_zipCodeLink zcl with (nolock) on zcl.zip3d_Id = z3d.zip3d_id inner join tbl_TER_Territories ter with (nolock) on ter.territoryId = zcl.territoryId inner join tbl_TER_TerritoryLevels l with (nolock) on l.levelId = ter.levelId and l.zipCodeMapping = 'STANDARD' and l.brokerLevel = 1 where ter.ownerId = @ownerId and ter.fsl_tablecode = @ownerType ), skuSales as ( select dstId, count( distinct skuId ) skuCount, case when ( select count(*) from tbl_DW_ProductHierarchy#_tableSuffix# where distributorSales ='Y' ) > 0 then count( distinct skuId ) / (( select count(*) from tbl_DW_ProductHierarchy#_tableSuffix# where distributorSales ='Y' ) * 1.0 ) else 0.0 end skuCoverage, min( purchasingMonth ) firstMonth, max( purchasingMonth ) lastMonth from tbl_DW_DistributorAnalyzer#_tableSuffix# with (nolock) group by dstId union all select parentDstId as dstId, count( distinct skuId ) skuCount, case when ( select count(*) from tbl_DW_ProductHierarchy#_tableSuffix# where distributorSales ='Y' ) > 0 then count( distinct skuId ) / (( select count(*) from tbl_DW_ProductHierarchy#_tableSuffix# where distributorSales ='Y' ) * 1.0 ) else 0.0 end skuCoverage, min( purchasingMonth ) firstMonth, max( purchasingMonth ) lastMonth from tbl_DW_DistributorAnalyzer#_tableSuffix# with (nolock) where dstId != parentDstId group by parentDstId ), purchasing as ( select distinct cho_id from tbl_DW_OperatorAnalyzer#_tableSuffix# where purchasingMonth >= @whitespaceCutoff ), scope1fs as ( select distinct fsl_choId as ownerId, 'CHO' as ownerType from tbl_OPR_ClientOperators opr with (nolock) inner join tbl_TPM_Claims c with (nolock) on c.partnerId = opr.operatorId and c.partnerType = 'OPR' and c.ownerId = opr.ownerId and c.ownerType = opr.fsltablecode where opr.fsl_choId > 0 and opr.ownerId = @ownerId and opr.fsltablecode = @ownerType union all select distinct fsl_dstId as ownerId, 'DST' as ownerType from tbl_CDR_Distributors cdr with (nolock) inner join tbl_TPM_Claims c with (nolock) on c.partnerId = cdr.cdr_recordId and c.partnerType = 'CDR' and c.ownerId = cdr.ownerId and c.ownerType = cdr.fsl_tablecode where cdr.fsl_dstId > 0 and cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType ), scopeClientActivity as ( select distinct opr.fsl_choId cho_id from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = tpf.mfr_Id and r.ownerType = @ownerType and r.requestId = tpf.rebateContractId inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerType = r.ownerType and ct.ownerId = r.ownerId and ct.contractId = r.contractId and ct.partnerType = 'OPR' inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = ct.partnerId and opr.ownerId = ct.ownerId and opr.fsltablecode = ct.ownerType and opr.fsl_choId > 0 where tpf.mfr_id = @ownerId and tpf.forecast_status = 'C' ), scopeClient as ( select distinct opr.fsl_choId as cho_id from scope1fs s inner join tbl_OPR_ClientOperators opr with (nolock) on opr.ownerId = s.ownerId and opr.fsltablecode = s.ownerType where opr.fsl_choId > 0 union select distinct cho_id from scopeClientActivity union select distinct cho_id from tbl_CHO_Operators with (nolock) where nullif( tastewiseId, '' ) is not null and coalesce( nullif( cho_acctClosed, ''), '0' ) = '0' ), scopedOperators 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_parentCo, c.cho_segmentId, c.tastewiseId from tbl_CHO_Operators c with (nolock) inner join scopeClient s on s.cho_id = c.cho_id where c.cho_type in ( 'I', 'U', 'O' ) and c.cho_address not like '285 Ellicott%' ) insert into tbl_DW_1fsUniverse#_tableSuffix# ( ownerType, ownerId, operatorChoId, operatorType, operatorName, operatorAddress, operatorCity, operatorState, operatorZipCode, operatorTerritoryId, operatorTerritory, operatorTerritoryPath, operatorRegion, operatorDivision, operatorCounty, segmentId, channelSegment, mainSegment, subSegment, operatorSegment, parentChoId, parentName, parentAddress, parentCity, parentState, parentZipCode, parentTerritoryId, parentTerritory, clientCount, averageClientVolume, rank1fs, totalClientVolume, firstMonth, lastMonth, primaryDstId, primaryDstName, primaryParentDstId, primaryParentDstName, secondaryDstId, secondaryDstName, secondaryParentDstId, secondaryParentDstName, inClientLibrary, primaryDstSkuSales, primaryDstSkuCoverage, primaryDstSkuLastMonth, secondaryDstSkuSales, secondaryDstSkuCoverage, secondaryDstSkuLastMonth, operatorPurchasing, whitespaceCutoffDate, numberOfPurchasingSources, percentScore, masterRank, tastewiseId, estMonthlyVolume, estMonthlyVolumeStatus ) select '#_ownerType#' ownerType, #_ownerId# as ownerId, c.cho_id operatorChoId, case when c.cho_type = 'I' then 'INDEPENDENT' when c.cho_type = 'U' then 'UNIT' else 'CHAINHQ' end operatorType, c.cho_name operatorName, c.cho_address operatorAddress, c.cho_city operatorCity, c.cho_state operatorState, c.cho_zipCode operatorZipCode, ter.territoryId as operatorTerritoryId, ter.territoryName as operatorTerritory, ter.territoryPath as operatorTerritoryPath, ter.regionName as operatorRegion, ter.divisionName as operatorDivision, ( select top 1 usps.countyName from tbl_USPS_CityStateDetails usps with (nolock) where usps.zipCode = c.cho_zipcode ) as operatorCounty, s.segmentId, s.channelSegment, s.mainSegment, s.subSegment, s.operatorSegment, p.cho_id parentChoId, p.cho_name parentName, p.cho_address parentAddress, p.cho_city parentCity, p.cho_state parentState, p.cho_zipcode parentZipcode, pter.territoryId as parentTerritoryId, pter.territoryName as parentTerritory, das.mfrs, das.monthlyClientAvg, row_number() over( ORDER BY das.monthlyClientAvg desc) as rank1fs, das.totalQty, das.firstMonth, das.lastMonth, das.primaryDstId, das.primaryDstName, das.primaryParentDstId, das.primaryParentDstName, das.secondaryDstId, das.secondaryDstName, das.secondaryParentDstId, das.secondaryParentDstName, case when exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = #_ownerId# and opr.fsltablecode = '#_ownerType#' and opr.fsl_choId = c.cho_id ) then 'Y' else 'N' end as inClientLibrary, case when ss1.dstId is not null then 'Y' else 'N' end, case when ss1.dstId is not null then ss1.skuCoverage else 0.0 end, case when ss1.dstId is not null then ss1.lastMonth else null end, case when ss2.dstId is not null then 'Y' else 'N' end, case when ss2.dstId is not null then ss2.skuCoverage else 0.0 end, case when ss2.dstId is not null then ss2.lastMonth else null end, case when pp.cho_id is null then 'N' else 'Y' end, @whitespaceCutoff, mfrs, das.percentScore, das.masterRank, c.tastewiseId, das.monthlyClientAvg * sft.factor, 'e' from scopedOperators c with (nolock) left outer join tbl_DW_OperatorAnalyzerRollup as das with (nolock) on das.cho_id = c.cho_Id left outer join segments s on s.segmentId = c.cho_segmentId left outer join tbl_CHO_Operators p with (nolock) on p.cho_id = c.cho_parentCo left outer join territories ter on ter.zipcode_3d = left( coalesce( c.cho_zipcode, '' ), 3 ) and ter.territoryRank = 1 left outer join territories pter on pter.zipcode_3d = left( coalesce( p.cho_zipcode, '' ), 3 ) and pter.territoryRank = 1 left outer join skuSales ss1 on ss1.dstId = coalesce( das.primaryDstId, das.primaryParentDstId ) left outer join skuSales ss2 on ss2.dstId = coalesce( das.secondaryDstId, das.secondaryParentDstId ) left outer join purchasing pp on pp.cho_id = c.cho_id left outer join [#_segmentFactorsTable#] sft with (nolock) on sft.operatorSegment = s.operatorSegment where 1 = 1 with o as ( select opr.oneFSId as fsl_choId, opr.operatorId, opr.salesRepId, opr.salesRepFirstName, opr.salesRepLastName, opr.lastInteractionDate, row_number() over( partition by opr.oneFSId order by opr.operatorId ) rankIt from tbl_DW_1fsUniverse#_tableSuffix# u with (nolock) /* inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = u.ownerId and opr.fsltablecode = u.ownerType and opr.fsl_choId = u.operatorChoId */ inner join tbl_DW_CRMOperators#_tableSuffix# opr on opr.ownerId = u.ownerId and opr.ownerType = u.ownerType and opr.oneFSId = u.operatorChoId ), clientOperators as ( select fsl_choId, operatorId, salesRepId, salesRepFirstName, salesRepLastName, lastInteractionDate from o where rankIt = 1 ) update u set u.clientOperatorId = o.operatorId, u.salesRepId = o.salesRepId, u.salesRepFirstName = o.salesRepFirstName, u.salesRepLastName = o.salesRepLastName, u.lastInteractionDate = o.lastInteractionDate from tbl_DW_1fsUniverse#_tableSuffix# u inner join clientOperators o on o.fsl_choId = u.operatorChoId declare @ownerId int = ; declare @ownerType varchar(3) = ; with territories as ( select ter.ownerId, ter.fsl_tablecode, m.fspro_userId as primaryManager, m.ownerId primaryManagerOwnerId, m.fsl_tablecode primaryManagerOwnerType, ter.territoryId, ter.parentTerritoryId from tbl_TER_Territories ter with (nolock) left outer join tbl_fspro_members m with (nolock) on m.ownerId > 0 and m.fspro_userId = ter.primaryManager where ter.ownerId = @ownerId and ter.fsl_tablecode = @ownerType ), territoryManagers as ( select ter.ownerId, ter.fsl_tablecode, ter.territoryId, ter.primaryManager, ter.primaryManagerOwnerId, ter.primaryManagerOwnerType, ter.parentTerritoryId , 1 as recurseLevel, case when nullif( ter.primaryManager, 0 ) is null then 'inherit' else 'explicit' end as origin from territorIes ter with (nolock) where ter.ownerId = @ownerId and ter.fsl_tablecode = @ownerType and nullif( ter.parentTerritoryId, 0 ) is null union all select ter.ownerId, ter.fsl_tablecode, ter.territoryId, case when ter.primaryManager is not null then ter.primaryManager else cte.primaryManager end as primaryManager, case when ter.primaryManagerOwnerId is not null then ter.primaryManagerOwnerId else cte.primaryManagerOwnerId end as primaryManagerOwnerId, case when ter.primaryManagerOwnerType is not null then ter.primaryManagerOwnerType else cte.primaryManagerOwnerType end as primaryManagerOwnerType, ter.parentTerritoryId , cte.recurseLevel + 1 recurseLevel, case when nullif( ter.primaryManager, 0 ) is null then 'inherit' else 'explicit' end as origin from territories ter with (nolock) inner join territoryManagers cte on cte.territoryId = ter.parentTerritoryId and cte.fsl_tablecode = ter.fsl_tablecode and cte.ownerId = ter.ownerId ), primaryManagers as ( select ownerId, fsl_tablecode, territoryId, primaryManager, primaryManagerOwnerId, primaryManagerOwnerType , origin from territoryManagers with (nolock) where primaryManager > 0 ), operators as ( select opr.ownerId, opr.fsltablecode, opr.operatorId, m.fspro_userId as accountOwnerUserId, m.ownerId as accountOwnerOwnerId, m.fsl_tablecode as accountOwnerOwnerType , case when m.fspro_userId is null then 'territory' else 'operator' end accountOwnerSource, pm.origin as primaryManagerSource from tbl_OPR_ClientOperators opr with (nolock) left outer join tbl_fspro_members m with (nolock) on m.ownerId > 0 and m.fspro_userId = opr.mfr_bsr_id inner join primaryManagers pm with (nolock) on pm.ownerId = opr.ownerId and pm.fsl_tablecode = opr.fsltablecode and pm.territoryId= opr.territoryId where opr.ownerId = @ownerId and opr.fsltablecode = @ownerType and opr.fsl_choId > 0 ) update u set u.accountOwnerUserId = opr.accountOwnerUserId, u.accountOwnerOwnerId = opr.accountOwnerOwnerId, u.accountOwnerOwnerType = opr.accountOwnerOwnerType from tbl_DW_1fsUniverse#_tableSuffix# u inner join operators opr with (nolock) on opr.ownerId = u.ownerId and opr.fsltablecode = u.ownerType and opr.operatorId = u.clientOperatorId update u set u.accountOwnerFullName = m.firstName + ' ' + m.lastName from tbl_DW_1fsUniverse#_tableSuffix# u inner join tbl_fspro_members m with (nolock) on m.ownerId = u.accountOwnerOwnerId and m.fsl_tablecode = u.accountOwnerOwnerType and m.fspro_userId = u.accountOwnerUserId with clientOperatorTerritoriesOverrides as ( select u.operatorChoId, t.territoryId, t.name as territoryName, t.territoryPath, t.regionName, t.divisionName from tbl_DW_1fsUniverse#_tableSuffix# u with (nolock) inner join tbl_OPR_CLientOperators opr with (nolock) on opr.ownerId = u.ownerId and opr.fsltablecode = u.ownerType and opr.operatorId = u.clientOperatorId inner join tbl_TER_Territories t with (nolock) on t.ownerId = opr.ownerId and t.fsl_tablecode = opr.fsltablecode and t.territoryId= opr.territoryId where t.territoryId != coalesce( u.operatorTerritoryId, 0 ) ) update u set u.operatorTerritoryId = cto.territoryId, u.operatorTerritory = cto.territoryName, u.operatorTerritoryPath = cto.territoryPath, u.operatorRegion = cto.regionName, u.operatorDivision = cto.divisionName from tbl_DW_1fsUniverse#_tableSuffix# u inner join clientOperatorTerritoriesOverrides cto on cto.operatorChoId = u.operatorChoId update oa set oa.averageClientVolume = u.averageClientVolume, oa.accountOwnerOwnerId = u.accountOwnerOwnerId, oa.accountOwnerOwnerType = u.accountOwnerOwnerType, oa.accountOwnerUserId = u.accountOwnerUserId, oa.accountOwnerFullName = u.accountOwnerFullName from tbl_DW_OperatorAnalyzer#_tableSuffix# oa inner join tbl_DW_1fsUniverse#_tableSuffix# u on u.operatorChoId = oa.cho_id update oa set oa.rollup_accountOwnerFullName = u.accountOwnerFullName from tbl_DW_OperatorAnalyzer#_tableSuffix# oa inner join tbl_DW_1fsUniverse#_tableSuffix# u on u.operatorChoId = oa.rollup_cho_id update oa set oa.masterRank = mr.masterRank from tbl_DW_OperatorAnalyzer#_tableSuffix# oa inner join tbl_DW_OperatorAnalyzerRollup mr on mr.cho_id = oa.cho_Id update o set o.averageClientVolume = u.averageClientVolume from tbl_DW_1fsOperators#_tableSuffix# o inner join tbl_DW_1fsUniverse#_tableSuffix# u on u.operatorChoId = o.choid update opr set opr.estMonthlyVolumeFrom1FS = coalesce( ofs.estMonthlyVolume, 0.0 ), -- ( select ofs.estMonthlyVolume from tbl_DW_1fsUniverse#_tableSuffix# ofs with (nolock) where ofs.operatorChoId = opr.fsl_choId ) opr.percentScoreFrom1fs = coalesce( ofs.percentScore, 0 ) from tbl_OPR_CLientOperators opr inner join tbl_DW_1fsUniverse#_tableSuffix# ofs with (nolock) on ofs.operatorChoId = opr.fsl_choId where opr.ownerId = and opr.fsltablecode = declare @mfrId int = ; select distinct bpl.ownerId as broker_id from tbl_BRO_Principals bpl with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.ownerId = bpl.ownerId and pt.ownerType = bpl.ownerType and pt.principalId = bpl.principalId inner join tbl_SMA_BrokerOfficeLink sma with (nolock) on sma.mfr_id = bpl.mfrId and sma.brokerid = bpl.ownerId inner join tbl_TER_Territories mt with (nolock) on mt.ownerId = sma.mfr_id and mt.fsl_tablecode = 'MFR' and nullif( mt.internalSalesId, '' ) = pt.mfrTerritoryNbr where bpl.ownerId > 0 and bpl.ownerType = 'BRO' and pt.hireStatus = 'A' and sma.mfr_id = @mfrId and 'MFR' = 'MFR' and exists ( select * from fsenablers..tbl_APP_Configurations cfg with (nolock) where cfg.applicationCode = 'MOBILE' and cfg.parameterName = 'apps.opr.mfrSalesDataSyndication.enabled' and cfg.parameterValue = 'true' and cfg.ownerType = 'BRO' and cfg.ownerId = bpl.ownerId )

Sleeping #now()#

drop table if exists [#_segmentFactorsTable#]; select top 1 batchId, batchTime, fileFormat, status, message from tbl_EAI_InboundBatches with (nolock) where ownerId = and ownerType = and status = 'RUNNING' and left( fileFormat, 1 ) = '$'