select object_id from sys.tables where name = 'tbl_DW_OperatorMembershipRanking#_tableSuffix#' declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @operatorId int = ; declare @operatorChoId int = ( select oneFSId from tbl_DW_CRMOperators#_tableSuffix# with (nolock) where operatorId = @operatorId ); declare @memberGroupType varchar(3) = ; with ranking as ( select omr.endMonth as lastPurchasingMonth, omr.memberGroupChoId, omr.memberGroupType, choMG.cho_name memberGroupName, omr.memberGroupRank, omr.volume3, omr.volume12, omr.typeVolume3, omr.typeVolume12, omr.portion3, omr.portion12, omr.avg12, omr.avg3, omr.trendRate, ( round( omr.portion3 * 100.0, 0 ) - round( omr.portion12 * 100.0, 0 )) / 100.0 portionVolumeChange , omr.mfr_id, mfr.mfr_name from [tbl_DW_OperatorMembershipRanking#_tableSuffix#] omr with (nolock) left outer join tbl_CHO_Operators choMG with (nolock) on choMG.cho_id = omr.memberGroupChoId left outer join tblManufacturers mfr with (nolock) on mfr.mfr_id = omr.mfr_id where omr.memberChoId = @operatorChoId and omr.ownerId = @ownerId and omr.ownerType = @ownerType and omr.mfr_id > 0= 0 and omr.memberGroupType = @memberGroupType ), affiliations as ( select org.oneFSId, org.companyName, a.affiliationStatus from tbl_CRM_Affiliations a with (nolock) inner join [tbl_DW_CRMOperators#_tableSuffix#] org with (nolock) on org.operatorId = a.orgId where a.ownerId = @ownerId and a.ownerType = @ownerType and a.partnerId = @operatorId and a.partnerType = 'OPR' and a.affiliationType = @memberGroupType union select org.oneFSId, org.companyName, 'A' affiliationStatus from tbl_CRM_InferredAffiliations a with (nolock) inner join [tbl_DW_CRMOperators#_tableSuffix#] org with (nolock) on org.operatorId = a.orgId where a.ownerId = @ownerId and a.ownerType = @ownerType and a.partnerId = @operatorId and a.partnerType = 'OPR' and a.affiliationType = @memberGroupType ) select r.lastPurchasingMonth, r.memberGroupChoId, r.memberGroupType, coalesce( a.companyName, r.memberGroupName ) memberGroupName, r.memberGroupRank, r.volume3, r.volume12, r.typeVolume3, r.typeVolume12, r.portion3, r.portion12, r.avg12, r.avg3, r.trendRate, r.portionVolumeChange , r.mfr_id, r.mfr_name , a.affiliationStatus from ranking r with (nolock) full outer join affiliations a on a.oneFSId = r.memberGroupChoId where r.memberGroupRank is not null or a.affiliationStatus = 'A' order by coalesce( a.affiliationStatus, 'I' ), coalesce( r.memberGroupRank, 99 ) , coalesce( r.mfr_name, 'ZZZZZZ' ) for json path, include_null_values declare @operatorId int = ; declare @operatorChoId int = ( select oneFSId from tbl_DW_CRMOperators#_tableSuffix# with (nolock) where operatorId = @operatorId ); declare @lastPurchasingMonth datetime; declare @memberGroups TABLE ( cho_id INT PRIMARY KEY, companyName VARCHAR(100) NOT NULL); with x as ( select gpoId_1, gpoId_2, gpoId_3, gpoId_4, cmcId_1, cmcId_2, cmcId_3 from tbl_DW_CRMOperators#_tableSuffix# with (nolock) where operatorId = @operatorId ) , y as ( select gpoId_1 memberGroupOperatorId from x where gpoId_1 is not null union all select gpoId_2 memberGroupOperatorId from x where gpoId_2 is not null union all select gpoId_3 memberGroupOperatorId from x where gpoId_3 is not null union all select gpoId_4 memberGroupOperatorId from x where gpoId_4 is not null union all select cmcId_1 memberGroupOperatorId from x where cmcId_1 is not null union all select cmcId_2 memberGroupOperatorId from x where cmcId_2 is not null union all select cmcId_3 memberGroupOperatorId from x where cmcId_3 is not null ) insert into @memberGroups (cho_id,companyName) select oneFSId, companyName from tbl_DW_CRMOperators#_tableSuffix# with (nolock) where operatorId in ( select memberGroupOperatorId from y ); with x as ( select ou.choId, max( oa.purchasingMonth ) lastPurchasingMonth from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join tbl_DW_1fsOperators#_tableSuffix# ou with (nolock) on ou.choId = oa.agreementOwnerChoId where oa.cho_id = @operatorChoId and agreementOwnerChoId in ( select cho_id from @memberGroups ) and exists ( select * from tbl_DW_OperatorPurchasing#_tableSuffix# op with (nolock) where op.skuId = oa.skuId and op.cho_id = oa.cho_id and op.purchasingStatus = 'B' ) group by ou.choId ) select @lastPurchasingMonth = min( lastPurchasingMonth ) from x; with x as ( select ou.operatorType, ou.choId, oa.agreementOwnerName, oa.mfr_id, oa.mfr_name, sum( forecast_qty ) volume12, sum( case when purchasingMonth >= dateadd( month, -2, @lastPurchasingMonth ) then forecast_qty else 0.0 end ) as volume3 from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join tbl_DW_1fsOperators#_tableSuffix# ou with (nolock) on ou.choId = oa.agreementOwnerChoId where oa.cho_id = @operatorChoId and agreementOwnerChoId in ( select cho_id from @memberGroups ) and purchasingMonth between dateadd( month, -11, @lastPurchasingMonth ) and @lastPurchasingMonth and exists ( select * from tbl_DW_OperatorPurchasing#_tableSuffix# op with (nolock) where op.skuId = oa.skuId and op.cho_id = oa.cho_id and op.purchasingStatus = 'B' ) group by ou.operatorType, ou.choId, oa.agreementOwnerName , oa.mfr_id, oa.mfr_name ) , y as ( select *, sum( volume12 ) over ( partition by operatorType , mfr_id ) as totalVolume12, sum( volume3 ) over ( partition by operatorType , mfr_id ) as totalVolume3 from x ) , z as ( select *, case when totalVolume12 != 0 then volume12 / totalVolume12 else 0.0 end as portionVolume12, case when totalVolume3 != 0 then volume3 / totalVolume3 else 0.0 end as portionVolume3 from y ) select @lastPurchasingMonth as lastPurchasingMonth, z.choId as memberGroupChoId, opr.operatorId memberGroupOperatorId, z.operatorType as memberGroupType, z.agreementOwnerName memberGroupName, z.mfr_id, z.mfr_name, z.volume3, z.volume12, z.totalVolume3, z.totalVolume12, z.portionVolume3, z.portionVolume12, ( round( z.portionVolume3 * 100.0, 0 ) - round( z.portionVolume12 * 100.0, 0 )) / 100.0 portionVolumeChange from z inner join tbl_DW_CRMOperators#_tableSuffix# opr with (nolock) on opr.oneFsId = z.choId for json path, include_null_values