select distinct dst.dstId, dst.dstCompanyType from tbl_CDR_DIstributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_recordId = select distinct fsl_dstId from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and ( cdr.cdr_dstCompanyType = 'B' or ( cdr.cdr_dstCompanyType = 'S' and cdr.cdr_specialAcctType = 'RDA' )) and cdr.cdr_territoryId in ( ) and fsl_dstId > 0 with distributors as ( select distinct dstId from tbl_DW_1fsDistributors#_tableSuffix# with (nolock) where ownerId = and ownerType = and #_col# in ( ) ), operatorPurchases as ( select 1 allocationRank, '#url.allocationType#' as allocationType, coalesce( oa.#_allocationIdColumn#, 0 ) as allocationId, coalesce( oa.#_allocationColumn#, 'Unknown' ) allocationName, oa.purchasingMonth, sum( oa.forecast_qty ) forecast_qty, case when oa.purchasingMonth >= then 'P1' else 'P0' end as periodCode ,oa.sku, oa.skuDesc, oa.product, oa.shortDesc, oa.catnum, oa.category, oa.productLineCode, oa.productLine, oa.budgetCategory, oa.territoryName, oa.dstName from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join distributors d on d.dstId = oa.dstId where oa.ownerId = and oa.ownerType = and ( 1 = 2 or oa.productHierarchyPath like ) and oa.skuId in ( ) and oa.prodId in ( ) and oa.categoryId in ( ) and oa.prodLine_id in ( ) and oa.budgetCategoryId in ( ) and exists ( select * from tbl_DW_DistributorAnalyzer#_tableSuffix# da with (nolock) where da.skuId = oa.skuId and da.purchasingMonth = oa.purchasingMonth and da.dstId = oa.dstId ) and (( oa.locationLevelAgreementOwner = 1 and oa.cho_id != oa.agreementOwnerChoId ) or oa.locationLevelAgreementOwner = 0 ) and oa.requestId is not null and ( oa.purchasingMonth between and or oa.purchasingMonth between and ) group by oa.#_allocationIdColumn#, oa.#_allocationColumn#, oa.purchasingMonth ,oa.sku, oa.skuDesc, oa.product, oa.shortDesc, oa.catnum, oa.category, oa.productLineCode, oa.productLine, oa.budgetCategory, oa.territoryName, oa.dstName ), distributorSales as ( select 0 allocationRank, 'streetBusiness' as allocationType, 0 as allocationId, 'STREET BUSINESS' as allocationName, da.purchasingMonth, sum( da.forecast_qty ) forecast_qty, case when da.purchasingMonth >= then 'P1' else 'P0' end as periodCode ,da.sku, da.skuDesc, da.product, da.shortDesc, da.catnum, da.category, da.productLineCode, da.productLine, da.budgetCategory, da.territoryName, da.dstName from tbl_DW_DistributorAnalyzer#_tableSuffix# da with (nolock) inner join distributors as d on d.dstId = da.dstId where da.ownerId = and da.ownerType = and ( 1 = 2 or da.productHierarchyPath like ) and da.skuId in ( ) and da.prodId in ( ) and da.categoryId in ( ) and da.prodLine_id in ( ) and da.budgetCategoryId in ( ) and ( da.purchasingMonth between and or da.purchasingMonth between and ) group by da.purchasingMonth ,da.sku, da.skuDesc, da.product, da.shortDesc, da.catnum, da.category, da.productLineCode, da.productLine, da.budgetCategory, da.territoryName, da.dstName ), adjDistributorSales as ( select ds.allocationRank, ds.allocationType, ds.allocationId, ds.allocationName, ds.purchasingMonth, ds.periodCode, -- ds.forecast_qty2, ds.forecast_qty - coalesce( ( select sum( op.forecast_qty ) from operatorPurchases op where op.purchasingMonth = ds.purchasingMonth and coalesce( op.sku, '' ) = coalesce( ds.sku, '' ) -- and coalesce( op.skuDesc, '' ) = coalesce( ds.skuDesc, '' ) and coalesce( op.dstName, '' ) = coalesce( ds.dstName, '' ) ) , 0.0 ) as forecast_qty ,ds.sku, ds.skuDesc, ds.product, ds.shortDesc, ds.catnum, ds.category, ds.productLineCode, ds.productLine, ds.budgetCategory, ds.territoryName, ds.dstName from distributorSales ds ), allocationSummary as ( select allocationRank, allocationType, allocationId, allocationName, periodCode, sum( forecast_qty ) forecast_qty ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName from operatorPurchases group by allocationRank, allocationType, allocationId, allocationName, periodCode ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName union all select allocationRank, allocationType, allocationId, allocationName, periodCode, sum( forecast_qty ) forecast_qty ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName from adjDistributorSales group by allocationRank, allocationType, allocationId, allocationName, periodCode ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName ) select ROW_NUMBER() OVER(ORDER BY allocationRank, P1 desc) AS rowNumber, '#_dataType#' as dataType, '#_dataId#' as dataId, allocationType, allocationId, allocationName, coalesce( P1, 0.0 ) as P1, coalesce( P0, 0.0 ) as P0, coalesce( P1, 0.0 ) - coalesce( P0, 0.0 ) change, case when coalesce( P0, 0.0 ) != 0 then ( coalesce( P1, 0.0 ) - coalesce( P0, 0.0 ) ) / coalesce( P0, 0.0 ) * 100.0 else null end as changePct, -- round( coalesce( p1, 0.0 ) / sum( coalesce( p1, 0.0 )) over() * 100.0, 1) as pctOfTotal --0.0 as pctOfTotal case when sum( coalesce( p1, 0.0 )) over() != 0 then round( coalesce( p1, 0.0 ) / sum( coalesce( p1, 0.0 )) over() * 100.0, 1) else null end as pctOfTotal ,sku, skuDesc, product, shortDesc, catnum, category, productLineCode, productLine, budgetCategory, territoryName, dstName from ( select * from allocationSummary ) as ps pivot ( sum( forecast_qty ) for periodCode in ( P1, P0 ) ) as pvt order by allocationRank, allocationName, territoryName, dstName, sku order by allocationRank, P1 desc for json auto, include_null_values