analyzerDataByMonth log
{ts '2026-04-15 18:26:44'} SQL
declare @lastPurchasingMonth as datetime = dateadd( month, 0, '03/01/2026' );
			declare @firstPurchasingMonth as datetime = dateadd( month, -23, '03/01/2026' );

			with

			

			

			-- _usePartnerList = NO 



			-- _useMembership=NO 



			x as (
			select

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

				
				sum( a.forecast_qty ) qty,

				
				( select count ( distinct aa.agreementOwnerName )
				    from tbl_DW_OperatorAnalyzer_BRO387 aa with (nolock)
					where aa.ownerId =  387
					and aa.ownerType =  'BRO'
					
					and aa.cho_id = a.cho_id
					
				    and aa.purchasingMonth <= @firstPurchasingMonth 
				    and aa.purchasingMonth >= @lastPurchasingMonth 
			    )
			    
			    as agreementOwnerCount

				, coalesce( a.operatorName, 'Unknown Operator' ) as operatorName,a.cho_id as cho_id,a.cho_type as operatortype

			  from tbl_DW_OperatorAnalyzer_BRO387 a with (nolock)

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

			  -- _useMembership = NO 

			  where a.ownerId = 387
			    and a.ownerType = 'BRO'

			    

			    and (
			        1 = 2

			    	
			    	or 1 = 1
			    	
			    )


				

					  -- operatorType = chainHQ_independent 
							   and ( a.cho_type in ( 'O', 'I' ) )
							   
				    and a.purchasingMonth <= @lastPurchasingMonth 
					
				    and a.purchasingMonth >= @firstPurchasingMonth 
				    
				


			group by

				a.purchasingMonth


				
					, a.operatorName,a.cho_id,a.cho_type 

			)

			
			,
			result as (
			select
				agreementOwnerCount,



			operatorName,cho_id,operatortype,
			
				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
				  operatorName,cho_id,operatortype 
			      ,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.*, case when s.cho_id is not null then 1 else 0 end selectedOperator
				  from resultNoSelections r
				  left outer join tmp_operatorAnalyzerSelections_BRO387__14032626 s
				     on s.cho_id = r.cho_id
			

			),

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

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