select object_id from sys.tables where name = drop table if exists [#_workTable#]; drop table if exists [#_skusTable#]; drop table if exists [#_operatorsTable#]; drop table if exists [#_operatorsAndSkusTable#]; declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @endDate datetime = ; declare @startDate datetime = ; declare @skuRankLimit int = ; with skus as ( select skuId, skuDesc, sku, profitabilityRank from [tbl_DW_ProductHierarchy#_tableSuffix#] ph with (nolock) where 1 = 2 where 1 = 12 and #applyFilter( _objectParams.productFilter )# -- and exists ( select * from [tbl_DW_SKURanking#_tableSuffix#] ds with (nolock) where ds.skuId = ph.skuId and ds.ranking <= 50 ) ) , skusWithVolume as ( select ph.sku, ph.skuId, ph.skuDesc, coalesce( sr.volume, 0 ) as volume, ph.profitabilityRank from skus ph with (nolock) left outer join [tbl_DW_SKURanking#_tableSuffix#] sr with (nolock) on sr.skuId = ph.skuId ), skusRanked as ( select *, row_number() over( order by volume desc, sku ) ranking from skusWithVolume ) select skuId, skuDesc, sku, profitabilityRank into [#_skusTable#] from skusRanked with (nolock) where ranking <= @skuRankLimit alter table [#_skusTable#] alter column skuId int not null; alter table [#_skusTable#] add constraint [pk_#_skusTable#] primary key ( skuId ); declare @ownerId int = ; declare @ownerType varchar(3) = ; with operators as ( select top 4000 operatorId, companyName, opr.mfr_bsr_id, cast( opr.distrib1_cdr_dstPath as varchar(200)) distrib1_cdr_dstPath, cast( opr.distrib2_cdr_dstPath as varchar(200)) distrib2_cdr_dstPath, distrib1, distrib2, cast( segmentPath as varchar(200)) as segmentPath, cast( territoryPath as varchar(200)) as territoryPath, coalesce( gpoid_1, 0 ) gpoid_1, coalesce( gpoid_2, 0 ) gpoId_2, coalesce( gpoId_3, 0 ) gpoId_3, coalesce( gpoId_4, 0 ) gpoId_4, coalesce( gpoId_5,0 ) gpoId_5, coalesce( cmcId_1, 0 ) cmcId_1, coalesce( cmcId_2, 0 ) cmcId_2, coalesce( cmcId_3, 0 ) cmcId_3 from [tbl_DW_CRMOperators#_tableSuffix#] opr with (nolock) where 1 = 2 where 1 = 12 and opr.oprCompanyType in ( '1','0') and #applyFilter( _objectParams.operatorFilter )# and exists ( select * from tbl_LST_PartnerListItems pli with (nolock) where pli.listId = and pli.partnerId = opr.operatorId ) -- and exists ( select * from tbl_DW_Opportunities#_tableSuffix# spl with (nolock) where spl.partnerId = opr.operatorId and spl.partnerType = 'OPR' ) and opr.territoryId in ( ) and opr.mfr_bsr_id in ( ) ) select * into [#_operatorsTable#] from operators with (nolock); alter table [#_operatorsTable#] alter column operatorId int not null; alter table [#_operatorsTable#] add constraint [pk_#_operatorsTable#] primary key (operatorId); select opr.operatorId, opr.companyName, opr.distrib1_cdr_dstPath, opr.distrib2_cdr_dstPath, opr.mfr_bsr_id, opr.segmentPath, opr.territoryPath, sku.skuId, sku.sku, sku.skuDesc, sku.profitabilityRank, opr.distrib1, opr.distrib2, opr.gpoid_1,opr.gpoId_2, opr.gpoId_3, opr.gpoid_4,opr.gpoId_5,opr.cmcid_1,opr.cmcId_2, opr.cmcId_3 into [#_operatorsAndSkusTable#] from [#_operatorsTable#] opr with (nolock) full join [#_skusTable#] sku with (nolock) on 1 = 1 where opr.operatorId is not null and sku.skuId is not null alter table [#_operatorsAndSkusTable#] alter column operatorId int not null; alter table [#_operatorsAndSKusTable#] alter column skuId int not null; alter table [#_operatorsAndSkusTable#] add constraint [pk_#_operatorsAndSkusTable#] primary key ( skuId, operatorId ); create index [idx_#_operatorsAndSkusTable#_skuSegment] on [#_operatorsAndSkusTable#] ( skuId, segmentPath ); create index [idx_#_operatorsAndSkusTable#_skuTerritory] on [#_operatorsAndSkusTable#] ( skuId, territoryPath ); declare @ownerId int = ; declare @ownerType varchar(3) = ; with purchasing as ( select os.skuId, os.sku, os.skuDesc, os.profitabilityRank, os.operatorId, os.companyName, os.distrib1, os.distrib2, os.gpoid_1, os.gpoId_2, os.gpoId_3, os.gpoid_4, os.gpoId_5, os.cmcid_1, os.cmcId_2, os.cmcId_3, ps.purchasingStatus as purchasingStatus, ps.confirmed_rolling_12_cases as cases, ps.confirmed_rolling_12_lbs as lbs, ps.confirmed_rolling_12_dollars as dollars, ps.quantityUOM as uom, ps.lastPurchaseMonth, ps.lastPurchaseAmt, ( select top 1 oa.dstName from [tbl_DW_OperatorAnalyzer#_tableSuffix#] oa with (nolock) where oa.cho_id = ps.cho_id and oa.purchasingMonth = ps.lastPurchaseMonth and oa.skuId = os.skuId ) lastPurchaseDst, -- ( select count(*) from [tbl_DW_Opportunities#_tableSuffix#] spl with (nolock) where spl.partnerId = os.operatorId and spl.partnerType = 'OPR' and spl.statusCode = 'C' and spl.skuId = os.skuId ) saleOpportunities, -- ( select count(*) from [tbl_DW_Opportunities#_tableSuffix#] spl with (nolock) where spl.partnerId = os.operatorId and spl.partnerType = 'OPR' and spl.statusCode = 'A' and spl.skuId = os.skuId ) activeOpportunities, -- ( select count(*) from [tbl_DW_Opportunities#_tableSuffix#] spl with (nolock) where spl.partnerId = os.operatorId and spl.partnerType = 'OPR' and spl.statusCode = 'R' and spl.skuId = os.skuId ) noSaleOpportunities, ( select top 1 spl.statusCode from [tbl_DW_Opportunities#_tableSuffix#] spl with (nolock) where spl.partnerId = os.operatorId and spl.partnerType = 'OPR' and spl.skuId = os.skuId and ( spl.statusCode != 'R' or ( spl.statusCode = 'R' and datediff( day, spl.estOrderDate, getDate() ) <= (18 * 30 ))) order by spl.updateDate desc ) lastOpportunityStatus, ( select top 1 spl.statusCode, spl.cases, spl.lbs, spl.dollars, spl.updateDate, spl.opportunityId, concat_ws( ' ', spl.effectiveRepFirstName, spl.effectiveRepLastName ) as effectiveRepFullName, spl.estOrderDate, spl.noSaleGeneralReason, case when spl.statusCode = 'A' then spl.stageName + ' - ' + convert( varchar, cast( spl.likelihoodPercent * 100.0 as int )) + '%' else spl.stageName end as stageNameFull from [tbl_DW_Opportunities#_tableSuffix#] spl with (nolock) where spl.partnerId = os.operatorId and spl.partnerType = 'OPR' and spl.skuId = os.skuId and ( spl.statusCode != 'R' or ( spl.statusCode = 'R' and datediff( day, spl.estOrderDate, getDate() ) <= (18 * 30 ))) order by spl.updateDate desc for json path, include_null_values, without_array_wrapper ) as lastOpportunity, case when exists ( select * from [tbl_DW_SKURankingBySegment#_tableSuffix#] sr with (nolock) where sr.skuId = os.skuId and sr.operatorSegment = os.segmentPath and sr.ranking <= 50 ) then 1 else 0 end as segmentTopSKU, case when exists ( select * from [tbl_DW_SKURankingByTerritory#_tableSuffix#] sr with (nolock) where sr.skuId = os.skuId and sr.territoryPath = os.territoryPath and sr.ranking <= 50 ) then 1 else 0 end as territoryTopSKU, case when exists ( select * from tbl_CDR_Stocking ds with (nolock) where ds.cdr_recordId in (os.distrib1, os.distrib2 ) and ds.stockingStatus = 'Y' and ds.skuId = os.skuId and ds.ownerID = @ownerId and ds.ownerType = @ownerType ) then 'Y' else 'N' end as stockingStatus, case when nullif( os.gpoid_1, 0 ) is null and nullif( os.cmcid_1, 0 ) is null then 'Y' when exists ( select * from tbl_CRM_AffiliationProducts ap with (nolock) where ap.ownerId = @ownerId and ap.ownerType = @ownerType and ap.affiliationOrgId in ( os.gpoid_1, os.gpoId_2, os.gpoId_3, os.gpoId_4, os.gpoId_5, os.cmcId_1, os.cmcId_2, os.cmcId_3 ) and left( ap.status, 1 ) = 'A' and cast( getDate() as date ) between coalesce( ap.startDate, cast( getDate() as date )) and coalesce( ap.endDate, cast( getDate() as date )) and ap.skuId = os.skuId ) then 'Y' else 'N' end as memberGroupApproved, cast( 0 as bit ) selected from [#_operatorsAndSkusTable#] os with (nolock) left outer join [tbl_DW_OperatorPurchasing#_tableSuffix#] ps with (nolock) on ps.ownerId = @ownerId and ps.ownerType = @ownerType and ps.operatorId = os.operatorId and ps.skuId = os.skuId -- and ps.confirmedPurchasingStatus != null -- and ps.confirmedVolumeQty > 0 -- and ps.confirmedPurchasingStatus = 'B' ), purchasing2 as ( select skuId, sku, skuDesc, profitabilityRank, operatorId, ( select p2.skuId, p2.operatorId, p2.purchasingStatus, p2.cases, p2.lbs, p2.dollars, p2.uom, p2.lastPurchaseMonth, p2.lastPurchaseAmt, p2.lastPurchaseDst, p2.segmentTopSKU, p2.territoryTopSKU, json_query( p2.lastOpportunity ) lastOpportunity, p2.lastOpportunityStatus, -- p2.saleOpportunities, p2.activeOpportunities, p2.noSaleOpportunities, p2.stockingStatus, p2.memberGroupApproved, p2.selected from purchasing p2 with (nolock) where p2.skuId = p1.skuId and p2.operatorId = p1.operatorId for json path, include_null_values, without_array_wrapper ) as purchasingData from purchasing p1 with (nolock) ) select os.skuId, os.sku, os.skuDesc, os.profitabilityRank, 'OPR_' + cast( os.operatorId as varchar ) as volumeIdentifier, ps.purchasingData into [#_workTable#] from [#_operatorsAndSkusTable#] os with (nolock) left outer join purchasing2 ps with (nolock) on ps.operatorId = os.operatorId and ps.skuId = os.skuId; -- create index [idx_#_workTable#_volumeIdentifer] on [#_workTable#] (volumeIdentifier); alter table [#_workTable#] alter column sku varchar(30) not null; alter table [#_workTable#] alter column skuDesc varchar(250) not null; alter table [#_workTable#] alter column volumeIdentifier varchar(34) not null; alter table [#_workTable#] add constraint [pk_#_worktable#] primary key ( skuId, sku, skuDesc, volumeIdentifier ); select distinct volumeIdentifier from [#_workTable#] with (nolock) with x as ( select distinct skuId, sku, skuDesc, profitabilityRank , cast( null as varchar) as [#_vi#] from [#_workTable#] with (nolock) ) #forJSON( 'x', attributes.datastore, 'sku' )# with x as ( select skuId, sku, skuDesc, profitabilityRank from [#_workTable#] with (nolock) ) #forJSON( 'x', attributes.datastore, 'sku' )# drop table if exists [#_skusTable#]; drop table if exists [#_operatorsTable#]; drop table if exists [#_operatorsAndSkusTable#]; select skuId, purchasingData from [#_workTable#] update vv set vv.purchasingData = ( select top 1 cast( json_value( v.purchasingData, '$.skuId' ) as int ) as skuId, cast( json_value( v.purchasingData, '$.operatorId' ) as int ) as operatorId, cast( json_value( v.purchasingData, '$.purchasingStatus' ) as varchar ) as purchasingStatus, cast( json_value( v.purchasingData, '$.cases' ) as float ) as cases, cast( json_value( v.purchasingData, '$.lbs' ) as float ) as lbs, cast( json_value( v.purchasingData, '$.dollars' ) as float ) as dollars, cast( json_value( v.purchasingData, '$.uom' ) as varchar ) as uom, cast( json_value( v.purchasingData, '$.segmentTopSKU' ) as int ) as segmentTopSKU, cast( json_value( v.purchasingData, '$.territoryTopSKU' ) as int ) as territoryTopSKU, json_query( v.purchasingData, '$.lastOpportunity' ) as lastOpportunity, cast( json_value( v.purchasingData, '$.lastOpportunityStatus' ) as varchar ) as lastOpportunityStatus, cast( json_value( v.purchasingData, '$.stockingStatus' ) as varchar ) as stockingStatus, cast( json_value( v.purchasingData, '$.memberGroupApproved' ) as varchar ) as memberGroupApproved, cast( json_value( v.purchasingData, '$.lastPurchaseMonth' ) as date ) as lastPurchaseMonth, cast( json_value( v.purchasingData, '$.lastPurchaseAmt' ) as float ) as lastPurchaseAmt, cast( json_value( v.purchasingData, '$.lastPurchaseDst' ) as varchar ) as lastPurchaseDst, cast( 0 as bit ) as selected from [#_workTable#] v where v.skuId = vv.skuId and v.volumeIdentifier = vv.volumeIdentifier for json auto, include_null_values, without_array_wrapper ) from [#_workTable#] vv where cast( json_value( vv.purchasingData, '$.selected' ) as bit ) = 1 update vv set vv.purchasingData = ( select top 1 cast( json_value( v.purchasingData, '$.skuId' ) as int ) as skuId, cast( json_value( v.purchasingData, '$.operatorId' ) as int ) as operatorId, cast( json_value( v.purchasingData, '$.purchasingStatus' ) as varchar ) as purchasingStatus, cast( json_value( v.purchasingData, '$.cases' ) as float ) as cases, cast( json_value( v.purchasingData, '$.lbs' ) as float ) as lbs, cast( json_value( v.purchasingData, '$.dollars' ) as float ) as dollars, cast( json_value( v.purchasingData, '$.uom' ) as varchar ) as uom, cast( json_value( v.purchasingData, '$.segmentTopSKU' ) as int ) as segmentTopSKU, cast( json_value( v.purchasingData, '$.territoryTopSKU' ) as int ) as territoryTopSKU, json_query( v.purchasingData, '$.lastOpportunity' ) as lastOpportunity, cast( json_value( v.purchasingData, '$.lastOpportunityStatus' ) as varchar ) as lastOpportunityStatus, cast( json_value( v.purchasingData, '$.stockingStatus' ) as varchar ) as stockingStatus, cast( json_value( v.purchasingData, '$.memberGroupApproved' ) as varchar ) as memberGroupApproved, cast( json_value( v.purchasingData, '$.lastPurchaseMonth' ) as date ) as lastPurchaseMonth, cast( json_value( v.purchasingData, '$.lastPurchaseAmt' ) as float ) as lastPurchaseAmt, cast( json_value( v.purchasingData, '$.lastPurchaseDst' ) as varchar ) as lastPurchaseDst, cast( #val( _selected )# as bit ) as selected from [#_workTable#] v where v.skuId = vv.skuId and v.volumeIdentifier = vv.volumeIdentifier for json auto, include_null_values, without_array_wrapper ) from [#_workTable#] vv where 1 = 1 where vv.skuId = and vv.volumeIdentifier = where coalesce( json_value( vv.purchasingData, '$.purchasingStatus' ), 'N' ) != 'B' and coalesce( json_value( vv.purchasingData, '$.lastOpportunityStatus' ), '?') != 'A' and json_value( vv.purchasingData, '$.segmentTopSKU' ) = 1 and json_value( vv.purchasingData, '$.territoryTopSKU' ) = 1 and json_value( vv.purchasingData, '$.stockingStatus' ) = 'Y' and json_value( vv.purchasingData, '$.memberGroupApproved' ) = 'Y' and vv.profitabilityRank > #val( _objectParams.picker.highlyProfitable )# and 1 = #val( _selectionGate )# and coalesce( json_value( vv.purchasingData, '$.lastOpportunityStatus'), '?' ) != 'R' and coalesce( json_value( vv.purchasingData, '$.lastOpportunityStatus'), '?' ) != 'C' select skuId, volumeIdentifier, volumeIdentifier + '_' + cast( skuId as varchar ) as selectionKey from [#_workTable#] v where cast( json_value( v.purchasingData, '$.selected' ) as bit ) = 1 for json auto, include_null_values with selections as ( select vm.skuId, vm.sku, vm.skuDesc, json_value( vm.purchasingData, '$.operatorId' ) operatorId from [#_workTable#] vm with (nolock) where cast( json_value( purchasingData, '$.selected' ) as bit ) = 1 ) select opr.operatorId, opr.companyName, opr.territoryId, opr.territoryName, opr.mfr_bsr_id, opr.primaryManagerFirstName + ' ' + opr.primaryManagerLastName primaryManagerFullName, opr.primaryManagerEmail, opr.segmentPath, opr.territoryPath, opr.distrib1, opr.distrib1_cdr_dstPath, opr.distrib2, opr.distrib2_cdr_dstPath, s.skuId, s.sku, s.skuDesc, concat_ws( ' ', opr.salesRepFirstName, opr.salesRepLastName ) as salesRepFullName from selections s inner join tbl_DW_CRMOperators#_tableSuffix# opr with (nolock) on opr.operatorId = s.operatorId order by companyName for json path, include_null_values with x as ( select skuId, sku, skuDesc, profitabilityRank, json_query( purchasingData ) as [#_objectParams.volumeIdentifier#] from [#_workTable#] with (nolock) where volumeIdentifier = ) #forJSON( 'x', attributes.datastore, 'sku' )#