analyzerDataByMonth log
{ts '2026-03-31 11:48:58'} SQL
declare @lastPurchasingMonth as datetime = dateadd( month, 0, '09/01/2025' );
			declare @firstPurchasingMonth as datetime = dateadd( month, -23, '09/01/2025' );

			with

			

			

			-- _usePartnerList = NO 



			-- _useMembership=NO 



			x as (
			select

				
				'M' + convert( varchar, datediff( month, @firstPurchasingMonth, a.purchasingMonth) + 1 ) mnth,

				
				sum( a.forecast_qty ) qty,

				
			    	0
			    
			    as agreementOwnerCount

				, a.agreementOwnerChoId as agreementOwnerChoId,coalesce( a.agreementOwnerName, 'Unknown Agreement Owner' ) as agreementOwnerName

			  from tbl_DW_OperatorAnalyzer_MFR207 a with (nolock)

			  left outer
				join tbl_DW_1fsOperators_MFR207 ao with (nolock)
			     on ao.ownerId= a.ownerId
			     and ao.ownerType = a.ownerType
			     and ao.choId = a.agreementOwnerChoId
			     

			  -- _useMembership = NO 

			  where a.ownerId = 207
			    and a.ownerType = 'MFR'

			    

			    and (
			        1 = 2

			    	
			    	or 1 = 1
			    	
			    )


				
					and ( not coalesce( a.requestId, 0 ) = 0 )
					

					  -- operatorType = 
				    and a.purchasingMonth <= @lastPurchasingMonth 
					
				    and a.purchasingMonth >= @firstPurchasingMonth 
				    
				
					
					and	a.requestId > 0 and ( ao.operatorType = 'INDEPENDENT' or coalesce( ao.locationLevelPurchasing, 'N' ) = 'N' or a.agreementOwnerChoId <> a.cho_id )
					


			group by

				a.purchasingMonth


				
					, a.agreementOwnerChoId,a.agreementOwnerName 

			)

			
			,
			result as (
			select
				agreementOwnerCount,



			agreementOwnerChoId,agreementOwnerName,
			
				coalesce( M1, 0 ) as M1,
			
				coalesce( M2, 0 ) as M2,
			
				coalesce( M3, 0 ) as M3,
			
				coalesce( M4, 0 ) as M4,
			
				coalesce( M5, 0 ) as M5,
			
				coalesce( M6, 0 ) as M6,
			
				coalesce( M7, 0 ) as M7,
			
				coalesce( M8, 0 ) as M8,
			
				coalesce( M9, 0 ) as M9,
			
				coalesce( M10, 0 ) as M10,
			
				coalesce( M11, 0 ) as M11,
			
				coalesce( M12, 0 ) as M12,
			
				coalesce( M1, 0 ) +
			
				coalesce( M2, 0 ) +
			
				coalesce( M3, 0 ) +
			
				coalesce( M4, 0 ) +
			
				coalesce( M5, 0 ) +
			
				coalesce( M6, 0 ) +
			
				coalesce( M7, 0 ) +
			
				coalesce( M8, 0 ) +
			
				coalesce( M9, 0 ) +
			
				coalesce( M10, 0 ) +
			
				coalesce( M11, 0 ) +
			
				coalesce( M12, 0 ) +
			

			0 as Y1,

			
				coalesce( M13, 0 ) as M13,
			
				coalesce( M14, 0 ) as M14,
			
				coalesce( M15, 0 ) as M15,
			
				coalesce( M16, 0 ) as M16,
			
				coalesce( M17, 0 ) as M17,
			
				coalesce( M18, 0 ) as M18,
			
				coalesce( M19, 0 ) as M19,
			
				coalesce( M20, 0 ) as M20,
			
				coalesce( M21, 0 ) as M21,
			
				coalesce( M22, 0 ) as M22,
			
				coalesce( M23, 0 ) as M23,
			
				coalesce( M24, 0 ) as M24,
			
				coalesce( M13, 0 ) +
			
				coalesce( M14, 0 ) +
			
				coalesce( M15, 0 ) +
			
				coalesce( M16, 0 ) +
			
				coalesce( M17, 0 ) +
			
				coalesce( M18, 0 ) +
			
				coalesce( M19, 0 ) +
			
				coalesce( M20, 0 ) +
			
				coalesce( M21, 0 ) +
			
				coalesce( M22, 0 ) +
			
				coalesce( M23, 0 ) +
			
				coalesce( M24, 0 ) +
			

			0 as Y2


			from (
			select *
			  from x
			 pivot(

			 	
				sum( qty )

				for mnth in ( M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12,
				              M13, M14, M15, M16, M17, M18, M19, M20, M21, M22, M23, M24 )
			 ) p
			) xx

			),

			resultNoSelections as (

			select
				  agreementOwnerChoId,agreementOwnerName 
			      ,y2, y1
			      ,y2 - y1 as difference, round( case when y1 > 0 then ( y2 - y1 ) / convert( float, y1 ) else 0 end, 2 ) variance from result

			),

			finalResult as (

			
				select r.*
				from resultNoSelections r
			

			),

			_result as (
				select *
				  from finalResult
				  where 1 = 1
				   and ( 1 = 1 )
			)

			
			select * from _result
			order by y2 desc
			for JSON AUTO, INCLUDE_NULL_VALUES

{ts '2026-03-31 14:02:19'} SQL
declare @lastPurchasingMonth as datetime = dateadd( month, 0, '09/01/2025' );
			declare @firstPurchasingMonth as datetime = dateadd( month, -23, '09/01/2025' );

			with

			

			

			-- _usePartnerList = NO 



			-- _useMembership=NO 



			x as (
			select

				
				'M' + convert( varchar, datediff( month, @firstPurchasingMonth, a.purchasingMonth) + 1 ) mnth,

				
				sum( a.forecast_qty ) qty,

				
			    	0
			    
			    as agreementOwnerCount

				, a.agreementOwnerChoId as agreementOwnerChoId,coalesce( a.agreementOwnerName, 'Unknown Agreement Owner' ) as agreementOwnerName

			  from tbl_DW_OperatorAnalyzer_MFR207 a with (nolock)

			  left outer
				join tbl_DW_1fsOperators_MFR207 ao with (nolock)
			     on ao.ownerId= a.ownerId
			     and ao.ownerType = a.ownerType
			     and ao.choId = a.agreementOwnerChoId
			     

			  -- _useMembership = NO 

			  where a.ownerId = 207
			    and a.ownerType = 'MFR'

			    

			    and (
			        1 = 2

			    	
			    	or 1 = 1
			    	
			    )


				
					and ( not coalesce( a.requestId, 0 ) = 0 )
					

					  -- operatorType = 
				    and a.purchasingMonth <= @lastPurchasingMonth 
					
				    and a.purchasingMonth >= @firstPurchasingMonth 
				    
				
					
					and	a.requestId > 0 and ( ao.operatorType = 'INDEPENDENT' or coalesce( ao.locationLevelPurchasing, 'N' ) = 'N' or a.agreementOwnerChoId <> a.cho_id )
					


			group by

				a.purchasingMonth


				
					, a.agreementOwnerChoId,a.agreementOwnerName 

			)

			
			,
			result as (
			select
				agreementOwnerCount,



			agreementOwnerChoId,agreementOwnerName,
			
				coalesce( M1, 0 ) as M1,
			
				coalesce( M2, 0 ) as M2,
			
				coalesce( M3, 0 ) as M3,
			
				coalesce( M4, 0 ) as M4,
			
				coalesce( M5, 0 ) as M5,
			
				coalesce( M6, 0 ) as M6,
			
				coalesce( M7, 0 ) as M7,
			
				coalesce( M8, 0 ) as M8,
			
				coalesce( M9, 0 ) as M9,
			
				coalesce( M10, 0 ) as M10,
			
				coalesce( M11, 0 ) as M11,
			
				coalesce( M12, 0 ) as M12,
			
				coalesce( M1, 0 ) +
			
				coalesce( M2, 0 ) +
			
				coalesce( M3, 0 ) +
			
				coalesce( M4, 0 ) +
			
				coalesce( M5, 0 ) +
			
				coalesce( M6, 0 ) +
			
				coalesce( M7, 0 ) +
			
				coalesce( M8, 0 ) +
			
				coalesce( M9, 0 ) +
			
				coalesce( M10, 0 ) +
			
				coalesce( M11, 0 ) +
			
				coalesce( M12, 0 ) +
			

			0 as Y1,

			
				coalesce( M13, 0 ) as M13,
			
				coalesce( M14, 0 ) as M14,
			
				coalesce( M15, 0 ) as M15,
			
				coalesce( M16, 0 ) as M16,
			
				coalesce( M17, 0 ) as M17,
			
				coalesce( M18, 0 ) as M18,
			
				coalesce( M19, 0 ) as M19,
			
				coalesce( M20, 0 ) as M20,
			
				coalesce( M21, 0 ) as M21,
			
				coalesce( M22, 0 ) as M22,
			
				coalesce( M23, 0 ) as M23,
			
				coalesce( M24, 0 ) as M24,
			
				coalesce( M13, 0 ) +
			
				coalesce( M14, 0 ) +
			
				coalesce( M15, 0 ) +
			
				coalesce( M16, 0 ) +
			
				coalesce( M17, 0 ) +
			
				coalesce( M18, 0 ) +
			
				coalesce( M19, 0 ) +
			
				coalesce( M20, 0 ) +
			
				coalesce( M21, 0 ) +
			
				coalesce( M22, 0 ) +
			
				coalesce( M23, 0 ) +
			
				coalesce( M24, 0 ) +
			

			0 as Y2


			from (
			select *
			  from x
			 pivot(

			 	
				sum( qty )

				for mnth in ( M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12,
				              M13, M14, M15, M16, M17, M18, M19, M20, M21, M22, M23, M24 )
			 ) p
			) xx

			),

			resultNoSelections as (

			select
				  agreementOwnerChoId,agreementOwnerName 
			      ,y2, y1
			      ,y2 - y1 as difference, round( case when y1 > 0 then ( y2 - y1 ) / convert( float, y1 ) else 0 end, 2 ) variance from result

			),

			finalResult as (

			
				select r.*
				from resultNoSelections r
			

			),

			_result as (
				select *
				  from finalResult
				  where 1 = 1
				   and ( 1 = 1 )
			)

			
			select * from _result
			order by y2 desc
			for JSON AUTO, INCLUDE_NULL_VALUES