declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @fiscalYearStart datetime = (select fiscalStartMonth from tbl_CRM_FiscalPeriods where ownerId = @ownerId and ownerType = @ownerType and periodType = 'Y'and getDate() between startDate and endDate ); declare @fiscalYearEnd datetime = dateadd( month, 11, @fiscalYearStart ); 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 @startMonth datetime = dateadd( month, -23, @currentMonth ); -- we are only ever going to look at 24 months of data with opa as ( select opa.cho_id, opa.operatorName, opa.skuId, opa.sku, opa.skuDesc, opa.purchasingMonth, -- opa.forecast_qty, cast( opa.forecast_qty as float ) * ph.qtyConversionToCases cases, cast( opa.forecast_qty as float ) * ph.qtyCOnversionToLbs lbs, cast( opa.forecast_qty as float ) * ph.unitPrice_USD dollars from tbl_DW_OperatorAnalyzer#_tableSuffix# opa with (nolock) 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 ), x as ( select opa.cho_id, opa.skuId, opa.sku, opa.operatorName, opa.skuDesc, opa.purchasingMonth, cases, lbs, dollars, -- rolling 12/24 numbers are from the date of last operator purchase case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 12 then cases else 0 end rolling12_cases, case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 12 then lbs else 0 end rolling12_lbs, case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 12 then dollars else 0 end rolling12_dollars, case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 12 then purchasingMonth else null end rolling12_month, case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 24 then cases else 0 end rolling24_cases, case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 24 then lbs else 0 end rolling24_lbs, case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 24 then dollars else 0 end rolling24_dollars, case when datediff( month, purchasingMonth, max( purchasingMonth ) over( partition by cho_id ) ) + 1 <= 24 then purchasingMonth else null end rolling24_month, case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then cases else 0 end ytd_cases, case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then lbs else 0 end ytd_lbs, case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then dollars else 0 end ytd_dollars, -- last purchase amount for operator/sku case when max( purchasingMonth ) over( partition by cho_id, opa.skuId ) = purchasingMonth then cases else 0 end last_cases, case when max( purchasingMonth ) over( partition by cho_id, opa.skuId ) = purchasingMonth then lbs else 0 end last_lbs, case when max( purchasingMonth ) over( partition by cho_id, opa.skuId ) = purchasingMonth then dollars else 0 end last_dollars, -- last purchase date for operator (disregarding sku) max( purchasingMonth ) over( partition by cho_id ) as operatorLastPurchasingMonth, max( case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) over( partition by cho_id ) as tyOperatorLastPurchasingMonth -- max( case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) over( partition by cho_id, skuId ) as tyOperatorSKULastPurchasingMonth from opa with (nolock) ) , x2 as ( select cho_id, skuId, operatorName, sku, skuDesc, operatorLastPurchasingMonth last_opr_purchase_month, tyOperatorLastPurchasingMonth ty_last_opr_purchase_month, -- elapsed months that the operator has been buying this product (within the window of time) datediff( month, min( purchasingMonth ), operatorLastPurchasingMonth ) + 1 as purchasing_months, -- purchasingMonthSpan count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) as ty_purchasing_month_count, min( purchasingMonth ) first_opr_sku_purchase_month, max( purchasingMonth ) last_opr_sku_purchase_month, -- lastPurchase max( last_cases ) as last_confirmed_cases, -- last_forecast_qty max( last_lbs ) as last_confirmed_lbs, -- last_forecast_qty max( last_dollars ) as last_confirmed_dollars, -- last_forecast_qty sum( cases ) as confirmed_total_cases, -- total_forecast_qty sum( lbs ) as confirmed_total_lbs, -- total_forecast_qty sum( dollars ) as confirmed_total_dollars, -- total_forecast_qty sum( rolling12_cases ) confirmed_rolling_12_cases, -- r12_forecast_qty sum( rolling12_lbs ) confirmed_rolling_12_lbs, -- r12_forecast_qty sum( rolling12_dollars ) confirmed_rolling_12_dollars, -- r12_forecast_qty sum( rolling12_cases ) / ( datediff( month, min( rolling12_month ), operatorLastPurchasingMonth ) + 1 ) as r12_raw_avg_cases, sum( rolling12_lbs ) / ( datediff( month, min( rolling12_month ), operatorLastPurchasingMonth ) + 1 ) as r12_raw_avg_lbs, sum( rolling12_dollars ) / ( datediff( month, min( rolling12_month ), operatorLastPurchasingMonth ) + 1 ) as r12_raw_avg_dollars, sum( ytd_cases ) as confirmed_ytd_cases, sum( ytd_lbs ) as confirmed_ytd_lbs, sum( ytd_dollars ) as confirmed_ytd_dollars, case when count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) > 0 then sum( ytd_cases ) / count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) else null end as ytd_raw_avg_cases2, case when count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) > 0 then sum( ytd_lbs ) / count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) else null end as ytd_raw_avg_lbs2, case when count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) > 0 then sum( ytd_dollars ) / count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) else null end as ytd_raw_avg_dollars2, case when datediff( month, @fiscalYearStart, tyOperatorLastPurchasingMonth ) >= 0 then sum( ytd_cases ) / ( datediff( month, @fiscalYearStart, tyOperatorLastPurchasingMonth ) + 1 ) else 0 end as ytd_raw_avg_cases, case when datediff( month, @fiscalYearStart, tyOperatorLastPurchasingMonth ) >= 0 then sum( ytd_lbs ) / ( datediff( month, @fiscalYearStart, tyOperatorLastPurchasingMonth ) + 1 ) else 0 end as ytd_raw_avg_lbs, case when datediff( month, @fiscalYearStart, tyOperatorLastPurchasingMonth ) >= 0 then sum( ytd_dollars ) / ( datediff( month, @fiscalYearStart, tyOperatorLastPurchasingMonth ) + 1 ) else 0 end as ytd_raw_avg_dollars, sum( rolling24_cases ) / ( datediff( month, min( rolling24_month ), operatorLastPurchasingMonth ) + 1 ) as r24_raw_avg_cases, sum( rolling24_lbs ) / ( datediff( month, min( rolling24_month ), operatorLastPurchasingMonth ) + 1 ) as r24_raw_avg_lbs, sum( rolling24_dollars ) / ( datediff( month, min( rolling24_month ), operatorLastPurchasingMonth ) + 1 ) as r24_raw_avg_dollars from x -- group by cho_id, operatorName, skuId, sku, skuDesc, operatorLastPurchasingMonth, tyOperatorLastPurchasingMonth ) , x3 as ( select cho_id, operatorName, skuId, sku, skuDesc, first_opr_sku_purchase_month, last_opr_purchase_month, purchasing_months, ty_last_opr_purchase_month, datediff( month, @fiscalYearStart, ty_last_opr_purchase_month ) + 1 ty_purchasing_months, ty_purchasing_month_count, --purchasing_total, confirmed_total_cases, confirmed_total_lbs, confirmed_total_dollars, ytd_raw_avg_cases, ytd_raw_avg_lbs, ytd_raw_avg_dollars, ytd_raw_avg_cases2, ytd_raw_avg_lbs2, ytd_raw_avg_dollars2, r12_raw_avg_cases as confirmed_avg_cases, r12_raw_avg_lbs as confirmed_avg_lbs, r12_raw_avg_dollars as confirmed_avg_dollars, r12_raw_avg_cases * 12 as annual_pace_cases, r12_raw_avg_lbs * 12 as annual_pace_lbs, r12_raw_avg_dollars * 12 as annual_pace_dollars, ytd_raw_avg_cases * ( datediff( month, ty_last_opr_purchase_month, @fiscalYearEnd ) ) as ty_remaining_pace_cases, ytd_raw_avg_lbs * ( datediff( month, ty_last_opr_purchase_month, @fiscalYearEnd ) ) as ty_remaining_pace_lbs, ytd_raw_avg_dollars * ( datediff( month, ty_last_opr_purchase_month, @fiscalYearEnd ) ) as ty_remaining_pace_dollars, --confirmed_ytd, confirmed_ytd_cases, confirmed_ytd_lbs, confirmed_ytd_dollars, --confirmed_rolling_12_months, confirmed_rolling_12_cases, confirmed_rolling_12_lbs, confirmed_rolling_12_dollars, --last_confirmed_purchase, last_confirmed_cases, last_confirmed_lbs, last_confirmed_dollars, last_opr_sku_purchase_month from x2 where cho_id = ), data as ( select x3.*, confirmed_ytd_cases + ty_remaining_pace_cases as ty_purchase_pace_cases, confirmed_ytd_lbs + ty_remaining_pace_lbs as ty_purchase_pace_lbs, confirmed_ytd_dollars + ty_remaining_pace_dollars as ty_purchase_pace_dollars from x3 ) #forJSON( 'data', attributes.datastore, 'sku' )#