opportunities log
{ts '2026-04-13 07:57:53'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 08:14:41'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 08:16:11'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:14:48'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
and 

	(
	

	(
	[territoryPath]
							
					like 'Affinity Group Corporate/Affinity Group Central%' 	
	)
or 

	(
	[territoryPath]
							
					like 'Affinity Group Corporate/Affinity Group Central/ACE - Indiana%' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:17:43'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:17:54'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
and 

	(
	

	(
	[territoryPath]
							
					like 'Affinity Group Corporate/Affinity Group Central%' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:18:14'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:18:24'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
and 

	(
	

	(
	[territoryPath]
							
					like 'Affinity Group Corporate/Affinity Group Central%' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:19:10'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:19:21'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
and 

	(
	

	(
	[territoryPath]
							
					like 'Affinity Group Corporate/Affinity Group Central%' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:19:25'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:19:34'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 100 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:20:13'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 2

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:20:26'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:20:36'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:20:44'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
and 

	(
	

	(
	[territoryPath]
							
					like 'Affinity Group Corporate/Affinity Group Central%' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:21:14'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:21:16'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 100 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER

{ts '2026-04-13 14:22:23'} SQL
with opportunities as (

		select * from tbl_DW_Opportunities_BRO387 spl with (nolock)
		where 1 = 1

		
		

	),

	opportunitiesFormatted as (
		select *,
		       sku + ' - ' + skuDesc as skuFull,
		       product + ' - ' + shortDesc as productFull,
			   catNum + ' - ' + category as categoryFull,
		       effectiveRepFirstName + ' ' + effectiveRepLastName as effectiveRepFullName,
		       case when statusCode = 'A' then stageName + ' - ' + convert( varchar, cast( likelihoodPercent * 100.0 as int )) + '%' else stageName end as stageNameFull,
		       case when budgetCategoryMgr_fspro_userId > 0 then budgetCategoryMgr_firstName + ' ' + budgetCategoryMgr_lastName else null end as budgetCategoryMgr_fullName,

			   case when partnerType = 'OPR' then oprClassification else cdrClassification end as partnerClassification,

			   case when partnerType = 'OPR' and oprClassificationId > 0 then partnerType + ':' + cast( oprClassificationId as varchar )
	            when partnerType = 'CDR' and cdrClassificationId > 0 then partnerType + ':' + cast( cdrClassificationId as varchar )
				when partnerType = 'OPR' then 'OPR:0'
				when partnerType = 'CDR' then 'CDR:0'
				else null end as partnerClassificationKey,

			   case when purchasingStatus is not null then 'Y' else 'N' end as has_purchasing_data,

				

			    cast( case when partnerType = 'OPR' and oprCompanyType = '1' then 1 else 0 end as bit ) as isChainHQ,
			    cast( case when partnerType = 'OPR' and oprCompanyType = '0' and independentYN = 0 then 1 else 0 end as bit ) as isChainUnit


			   , concat_ws( '|', objName1, objName2, objName3, objName4, objName5 ) allObjNames
			   
			   
		from opportunities
	)



	


, opportunitiesFormatted_mappedData as (

	select *
		
	  from opportunitiesFormatted

)

, opportunitiesFormatted_preData as (

	
	select *
	  from opportunitiesFormatted_mappedData with (nolock)

	
	
	where 

	(
	( 1 = 1 ) and 

	(
	

	(
	

	(
	[estOrderDate]
							>= '2026-01-01T00:00:00' 	
	)
and 

	(
	[estOrderDate]
							<= '2026-12-31T00:00:00' 	
	)
	
	)
and 

	(
	

	(
	[partnerType]
							= 'CDR' 	
	)
	
	)
	
	)
	
	)



	)

	, opportunitiesFormatted_data as (

		select * 
		        
		  from opportunitiesFormatted_preData
		  

	)



	, opportunitiesFormatted_return as (
		select * from opportunitiesFormatted_data
		where 1 = 1


	order by opportunityId
		
		
		 offset 0 rows fetch next 100 rows only
	
	)

	
	, opportunitiesFormatted_result as (
		select ( select count(*) from opportunitiesFormatted_data ) as totalCount, 
			   
					( select sum( cases ) from opportunitiesFormatted_data )
					 as [totalSummary_0],
			   
					( select sum( lbs ) from opportunitiesFormatted_data )
					 as [totalSummary_1],
			   
					( select sum( dollars ) from opportunitiesFormatted_data )
					 as [totalSummary_2],
			   
					( select sum( commissionAmt ) from opportunitiesFormatted_data )
					 as [totalSummary_3],
			   
					( select sum( impactCases ) from opportunitiesFormatted_data )
					 as [totalSummary_4],
			   
					( select sum( impactLbs ) from opportunitiesFormatted_data )
					 as [totalSummary_5],
			   
					( select sum( impactDollars ) from opportunitiesFormatted_data )
					 as [totalSummary_6],
			   
		       ( select * from opportunitiesFormatted_return for json auto, include_null_values ) as data
		)


	select * from opportunitiesFormatted_result
	for JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER