distributionAnalyzerData log
{ts '2026-03-16 10:51:15'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:52:23'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:55:06'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:55:26'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:56:06'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:56:25'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:57:09'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:57:27'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:58:15'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:58:37'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST/FS DIST 10 - NEW YORK|NEW JERSEY/FS UPSTATE NEW YORK TERRITORY%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 10:59:24'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:01:55'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:02:18'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:03:39'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:04:10'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:04:42'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:05:01'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:05:21'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:06:54'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:07:18'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:08:18'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:08:35'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:09:05'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:09:35'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:09:53'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:25:45'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	[dstId]
							= '-1' 	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-03-16 11:26:00'} SQL
declare @ownerId int = 207 ;
	declare @ownerType varchar(3) = 'MFR' ;

	declare @baseMonth date = '2024-03-01' ;

	with 
	
	
	allDistributors as (
		
		select distinct cdr_dstPath, oneFsId as dstId, territoryPath, buyingGroupId, buyingGroupId2, buyingGroupId3, accountOwner_fspro_userId, accountOwner_ownerType, classificationId
		  from tbl_DW_DistributorSales_MFR207 ds with (nolock)
	)

	, distributors as (
		select distinct dstId
		  from allDistributors with (nolock)
		 where 

	(
	

	(
	

	(
	[territoryPath]
							
					like 'Kellogg''s Corporate/FOODSERVICE/REGION 1 - FS NORTHEAST%' 	
	)
	
	)
	
	)


	)

	, products as (

		select *
		  from tbl_DW_ProductHierarchy_MFR207 with (nolock)
		  where ( 1 = 1 )
	)

	, 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

		 , oa.agreementOwnerName,oa.agreementOwnerChoId 

		from tbl_DW_OperatorAnalyzer_MFR207 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_MFR207 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 '2025-03-01'  and '2026-02-01' 
		            or
		            oa.purchasingMonth between '2024-03-01'  and '2025-02-01' 
		          )
		group by oa.purchasingMonth , oa.agreementOwnerName,oa.agreementOwnerChoId 
		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.cases ) as forecast_qty
		

		from tbl_DW_DistributorSales_MFR207 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 '2025-03-01'  and '2026-02-01' 
				or
				da.salesPeriod between '2024-03-01'  and '2025-02-01' 
		    )
	  group by da.salesPeriod 

	)

	, salesWork as (

		select periodCode, purchasingMonth, forecast_qty
		       
		  from sales with (nolock)

		union all

		select periodCode, purchasingMonth, forecast_qty * -1 as forecast_qty 
		       
		   from opa with (nolock)

	)

	, adjSales as (

		select periodCode, purchasingMonth, sum( forecast_qty ) forecast_qty
			
           from salesWork with (nolock)
		group by periodCode, purchasingMonth 
	)

	, combined as (

		select 0 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , agreementOwnerName,agreementOwnerChoId 
		   from opa with (nolock)

		union all

		select 1 as allocationRank, periodCode, purchasingMonth, forecast_qty 
		       , 'STREET SALES' as agreementOwnerChoId,null as agreementOwnerChoId 
		from adjSales with (nolock)

	)

	, result as (

		select p.allocationRank ,p.P1,p.P2,p.P3,p.P4,p.P5,p.P6,p.P7,p.P8,p.P9,p.P10,p.P11,p.P12,p.P13,p.P14,p.P15,p.P16,p.P17,p.P18,p.P19,p.P20,p.P21,p.P22,p.P23,p.P24 , p.agreementOwnerName,p.agreementOwnerChoId 
		from (
			select allocationRank, periodCode, forecast_qty 
				, agreementOwnerName,agreementOwnerChoId 
			from combined with (nolock)
		) s
		pivot( 
			sum( forecast_qty ) for periodCode in ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16,P17,P18,P19,P20,P21,P22,P23,P24 )
		) p
	)

	, result2 as (

		select r.*,  coalesce( P1, 0.0 )+ coalesce( P2, 0.0 )+ coalesce( P3, 0.0 )+ coalesce( P4, 0.0 )+ coalesce( P5, 0.0 )+ coalesce( P6, 0.0 )+ coalesce( P7, 0.0 )+ coalesce( P8, 0.0 )+ coalesce( P9, 0.0 )+ coalesce( P10, 0.0 )+ coalesce( P11, 0.0 )+ coalesce( P12, 0.0 ) as R0
		,  coalesce( P13, 0.0 )+ coalesce( P14, 0.0 )+ coalesce( P15, 0.0 )+ coalesce( P16, 0.0 )+ coalesce( P17, 0.0 )+ coalesce( P18, 0.0 )+ coalesce( P19, 0.0 )+ coalesce( P20, 0.0 )+ coalesce( P21, 0.0 )+ coalesce( P22, 0.0 )+ coalesce( P23, 0.0 )+ coalesce( P24, 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)

	)

	


, result3_mappedData as (

	select *
		
	  from result3

)

, result3_preData as (

	
	select *
	  from result3_mappedData with (nolock)

	
	
	where ( 1 = 1 ) 


	)

	, result3_data as (

		select * 
		        
		  from result3_preData
		  

	)



	, result3_return as (
		select * from result3_data
		where 1 = 1


	order by allocationRank , R1  desc, agreementOwnerName 
		
		
		 offset 0 rows fetch next 10000000 rows only
	
	)

	
	, result3_result as (
		select ( select count(*) from result3_data ) as totalCount, 
			   
		       ( select * from result3_return for json auto, include_null_values ) as data
		)


	select * from result3_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER