declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @lastPurchasingMonth datetime = ( select top 1 purchasingMonth from tbl_DW_PurchasingMonths#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and purchasingMonth <= getDate() order by purchasingMonth desc ); declare @currentMonth datetime = dateadd( month, -1, coalesce( @lastPurchasingMonth, (select fiscalStartMonth from tbl_CRM_FiscalPeriods where ownerId = @ownerId and ownerType = @ownerType and periodType = 'M'and getDate() <= endDate and percentComplete > 0) )); declare @fiscalYearStart datetime = (select fiscalStartMonth from tbl_CRM_FiscalPeriods where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y'and @currentMonth between fiscalStartMonth and fiscalEndMonth ); declare @fiscalYearEnd datetime = dateadd( month, 11, @fiscalYearStart ); declare @startMonth datetime = dateadd( month, -23, @currentMonth ); -- we are only ever going to look at 24 months of data declare @fiscalLastYearStart datetime = (select top 1 fiscalStartMonth from tbl_CRM_FiscalPeriods where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y'and fiscalStartMonth < @fiscalYearStart order by fiscalStartMonth desc ); declare @fiscalLastYearEnd datetime = dateadd( month, 11, @fiscalLastYearStart ); declare @fiscalLastYearCutoff datetime = dateadd( month, -12, @currentMonth ); with dates as ( select @fiscalYearStart fiscalYearStart, @fiscalYearEnd fiscalYearEnd, @lastPurchasingMonth lastPurchasingMonth, @currentMonth currentMonth, @currentMonth opa_lastPurchasingMonth, @currentMonth fiscalYearCutoff, datediff( month, @fiscalYearStart, @currentMonth ) + 1 as opa_windowMonths, @startMonth startMonth, @fiscalLastYearStart fiscalLastYearStart, @fiscalLastYearEnd fiscalLastYearEnd, @fiscalLastYearCutoff fiscalLastYearCutoff ) select * from dates for json auto, include_null_values declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @fiscalYearStart datetime = ; declare @fiscalYearEnd datetime = ; declare @lastPurchasingMonth datetime = ; declare @currentMonth datetime = ; declare @startMonth datetime = ; declare @fiscalLastYearStart datetime = ; declare @fiscalLastYearEnd datetime = ; declare @fiscalLastYearCutoff datetime = ; with blank as ( select ownerId, ownerType, cho_id as choId, operatorName, min( purchasingMonth ) firstPurchasingMonth, max( purchasingMonth ) lastPurchasingMonth, 1 as activeLocation, 0 as rollup_cho_id, 'I' as rollup_cho_type, '' as rollup_operatorName, '' as rollup_operatorAddress, '' as rollup_operatorCity, '' as rollup_operatorState, '' as rollup_operatorZipCode from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType= @ownerType and cho_id = 0 group by ownerId, ownerType, cho_id, operatorName ), locations as ( select m.ownerId, m.ownerType, m.memberCHOId as choId, m.memberName operatorName, datefromparts( Year( m.affiliationDate ), month( m.affiliationDate ), 1 ) firstDate, dateadd( day, -1, dateadd( month, 1, datefromParts( year(coalesce( m.deaffiliationDate, datefromparts( 3000, 12, 31 ))), month(coalesce( m.deaffiliationDate, datefromparts( 3000, 12, 31 ))), 1 ) )) lastDate, case when m.deaffiliationDate is not null then 0 else 1 end activeLocation, mg.choId as rollup_cho_id, case when mg.operatorType = 'CHAINHQ' then 'O' else left( mg.operatorType, 1 ) end as rollup_cho_type, mg.operatorName as rollup_operatorName, mg.operatorAddress as rollup_operatorAddress, mg.operatorCity as rollup_operatorCity, mg.operatorState as rollup_operatorState, mg.operatorZipCode as rollup_operatorZipCode from tbl_DW_1fsoperatorMemberships#_tableSuffix# m with (nolock) inner join tbl_DW_1fsOperators#_tableSuffix# mm with (nolock) on mm.ownerId = m.ownerId and mm.ownerType = m.ownerType and mm.choId = m.memberChoId inner join tbl_DW_1fsOperators#_tableSuffix# mg with (nolock) on mg.ownerId = m.ownerId and mg.ownerType = m.ownerType and mg.choId = m.memberGroupChoId where m.ownerId = @ownerId and m.ownerType= @ownerType and mm.operatorType in ( 'UNIT', 'INDEPENDENT' ) union all select ownerId, ownerType, choId, operatorName, firstPurchasingMonth as firstDate, dateadd( day, -1, dateadd( month, 1, lastPurchasingMonth )) as lastDate, activeLocation, rollup_cho_id, rollup_cho_type, rollup_operatorName, rollup_operatorAddress, rollup_operatorCity, rollup_operatorState, rollup_operatorState from blank ), opa as( select , ou.clientOperatorId 'dstId:' + cast( dstId as varchar ) as distributorKeyId opa.#_opaCol# , opa.purchasingMonth, cast( opa.forecast_qty as float ) * ph.qtyConversionToCases cases, cast( opa.forecast_qty as float ) * ph.qtyCOnversionToLbs lbs, cast( opa.totalPrice_retail as float ) dollars, cast( opa.forecast_qty as float ) quantity, cast( opa.totalPrice_net as float ) netDollars from tbl_DW_OperatorAnalyzer#_tableSuffix# opa with (nolock) inner join locations l on l.ownerId = opa.ownerId and l.ownerType = opa.ownerType and l.choId = opa.cho_id left outer join tbl_DW_1fsOperators#_tableSuffix# opao with (nolock) on opao.ownerId= opa.ownerId and opao.ownerType = opa.ownerType and opao.choId = opa.agreementOwnerChoId inner join tbl_DW_1fsUniverse#_tableSuffix# ou with (nolock) on ou.operatorChoId = opa.cho_id inner join tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) on ph.ownerId = opa.ownerId and ph.ownerType = opa.ownerType and ph.skuId = opa.skuId where opa.purchasingMonth <= @currentMonth and opa.purchasingMonth >= @startMonth and opa.cho_type in ( 'I', 'O' ) and opa.requestId > 0 and ( opao.operatorType = 'INDEPENDENT' or coalesce( opao.locationLevelPurchasing, 'N' ) = 'N' or opa.agreementOwnerChoId <> opa.cho_id ) and ( opao.operatorType = 'INDEPENDENT' or coalesce( opao.locationLevelPurchasing, 'N' ) = 'N' or opa.agreementOwnerChoId <> opa.cho_id ) ---> and opa.requestId > 0 and ( opao.operatorType = 'INDEPENDENT' or coalesce( opao.locationLevelPurchasing, 'N' ) = 'N' or opa.agreementOwnerChoId <> opa.cho_id ) and opa.cho_type in ( 'I', 'O' ) -- and opa.rollup_cho_id = opa.cho_id and opa.territoryId in ( ) and ( or opa.territoryPath like ) and ( or opa.productHierarchyPath like ) and opa.mfr_id in ( ) and opa.accountOwnerUserId in ( ) and ( or opa.segmentPath like ) ) , data as ( select *, case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then [#_uom#] else 0.0 end * 1.0 ytd, case when datediff( month, @fiscalLastYearStart, purchasingMonth ) >= 0 and datediff( month, purchasingMonth, @fiscalLastYearCutoff ) >= 0 then [#_uom#] else 0 end * 1.0 lytd from opa ) , oprStats as ( select opr.operatorId, opr.lastInteractionDate, opr.nextInteractionDate, sum( spl.#_pipelineVolCol# ) activeOpportunityVolume from tbl_DW_CRMOperators#_tableSuffix# opr with (nolock) left outer join tbl_DW_Opportunities#_tablesuffix# spl with (nolock) on spl.partnerId = opr.operatorId and spl.partnerType = 'OPR' and spl.ownerId = opr.ownerId and spl.ownerType = opr.ownerType and spl.statusCode = 'A' group by opr.operatorId, opr.lastInteractionDate, opr.nextInteractionDate ) , grouped as ( select #_groupColumns#, sum( lytd) lytd, sum( ytd ) ytd, sum( ytd ) - sum( lytd) tyChange, case when sum( lytd ) != 0 then (sum( ytd ) - sum( lytd )) / sum( lytd ) else null end changePct , max( oprStats.lastInteractionDate ) lastInteractionDate , max( oprStats.nextInteractionDate ) nextInteractionDate , max( oprStats.activeOpportunityVolume ) activeOpportunityVolume from data left outer join oprStats on oprStats.operatorId = data.clientOperatorId group by #_groupColumns# ) , result as ( select *, case when changePct < 0.0 then abs( changePct ) else null end as declinePct from grouped where lytd > 0 and changePct < 0.0 and lytd >= and changePct <= ) #forJson( 'result', attributes.dataStore, 'tyChange' )#