declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @baseMonth date = ; with allDistributors as ( select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId from tbl_DW_DistributorSales#_tableSuffix# ds with (nolock) ) , distributors as ( select distinct dstId from allDistributors with (nolock) where #applyFilter( _distributorFilter )# ) , products as ( select * from tbl_DW_ProductHierarchy#_tableSuffix# with (nolock) where #applyFilter( _productFilter )# ) , opa as ( select 'P' + cast( datediff( month, @baseMonth, oa.purchasingMonth ) + 1 as varchar) as periodCode, oa.purchasingMonth, sum( round( oa.forecast_qty, 2 ) ) forecast_qty #operatorColumns( "oa" )# #productColumns( "ph" )# from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join distributors d with (nolock) on d.dstId = oa.dstId inner join products ph with (nolock) on ph.skuId = oa.skuId where 1 = 1 -- this is not necessarily true (that the distributor has purchased the sku in the same month that the operator bought the sku) -- exists ( select * from tbl_DW_DistributorSales#_tableSuffix# da with (nolock) where da.skuId = oa.skuId and da.salesPeriodType = 'MONTH' and da.salesPeriod = oa.purchasingMonth and da.oneFSId = oa.dstId ) and (( oa.locationLevelAgreementOwner = 1 and oa.cho_id != oa.agreementOwnerChoId and oa.cho_type not in ( 'G', 'C', 'O' )) or oa.locationLevelAgreementOwner = 0 ) and oa.requestId is not null and ( oa.purchasingMonth between and or oa.purchasingMonth between and ) group by oa.purchasingMonth #operatorColumns("oa")# #productColumns( "ph" )# having sum( round( oa.forecast_qty, 2 ) ) > 0.0 ) , sales as ( select 'P' + cast( datediff( month, @baseMonth, da.salesPeriod ) + 1 as varchar) as periodCode, da.salesPeriod as purchasingMonth, sum( da.#_distributorSalesUOM# ) as forecast_qty #productColumns( "ph" )# from tbl_DW_DistributorSales#_tableSuffix# da with (nolock) inner join distributors as d with (nolock) on d.dstId = da.oneFSId inner join products ph with (nolock) on ph.skuId = da.skuId where da.salesPeriodType = 'MONTH' and ( da.salesPeriod between and or da.salesPeriod between and ) group by da.salesPeriod #productColumns( "ph" )# ) , salesWork as ( select periodCode, purchasingMonth, forecast_qty #productColumns()# from sales with (nolock) union all select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty #productColumns()# from opa with (nolock) ) , adjSales as ( select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty #productColumns()# from salesWork with (nolock) group by periodCode, purchasingMonth #productColumns()# ) , combined as ( select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty #operatorColumns()# #productColumns()# from opa with (nolock) union all select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty #streetColumns()# #productColumns()# from adjSales with (nolock) ) , result as ( select p.allocationRank ,p.P#_px# #operatorColumns("p")# #productColumns("p")# from ( select allocationRank, periodCode, forecast_qty #operatorColumns()# #productColumns()# from combined with (nolock) ) s pivot( sum( forecast_qty ) for periodCode in ( ,P#_px# ) ) p ) , result2 as ( select r.*, + coalesce( P#_px#, 0.0 ) as R0 , + coalesce( P#_px#, 0.0 ) as R1 from result r with (nolock) ) , result3 as ( select r.*, R1 - R0 as D1, case when R0 <> 0 then ( R1 - R0 ) / R0 else null end as V1 from result2 r with (nolock) ) #forJSON( 'result3', attributes.datastore, 'allocationRank' )#