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) )); select @currentMonth as mostRecentPurchasingMonth select unitOfMeasurePlural from tblManufacturers with (nolock) where mfr_id =

#_hqLastPurchasingMonthDataTable#

drop table if exists #_hqLastPurchasingMonthDataTable#; with hq as ( select distinct cho_id from tbl_DW_OperatorAnalyzer#_tableSuffix# o with (nolock) where cho_type = 'O' and agreementOwnerChoId is null ), hqAgreementOwners as ( select cho_id, max( purchasingMonth ) as lastPurchasingMonth from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where cho_id = agreementOwnerChoId group by cho_id ) select hq.cho_id, max( ao.lastPurchasingMonth ) lastPurchasingMonth into #_hqLastPurchasingMonthDataTable# from tbl_DW_OperatorAnalyzer#_tableSuffix# u with (nolock) inner join hq on u.rollup_cho_id = hq.cho_id inner join hqAgreementOwners as ao on ao.cho_id = u.agreementOwnerChoId where u.cho_type = 'U' group by hq.cho_id; alter table #_hqLastPurchasingMonthDataTable# add constraint PK_#_hqLastPurchasingMonthDataTable# primary key ( cho_id )

#encodeforHTML( _r.sql )#

#_purchasingDataTable2#

drop table if exists #_purchasingDataTable2#; 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 ); ---> 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 opa as ( select opa.cho_id, opa.skuId, -- opa.operatorName, opa.sku, opa.skuDesc, opa.purchasingMonth, 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, case when datediff( month, @fiscalLastYearStart, purchasingMonth ) >= 0 and datediff( month, purchasingMonth, @fiscalLastYearCutoff ) >= 0 then cases else 0 end * 1.0 lytd_cases, case when datediff( month, @fiscalLastYearStart, purchasingMonth ) >= 0 and datediff( month, purchasingMonth, @fiscalLastYearCutoff ) >= 0 then lbs else 0 end * 1.0 lytd_lbs, case when datediff( month, @fiscalLastYearStart, purchasingMonth ) >= 0 and datediff( month, purchasingMonth, @fiscalLastYearCutoff ) >= 0 then dollars else 0 end * 1.0 lytd_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 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 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, sum( lytd_cases ) as confirmed_lytd_cases, sum( lytd_lbs ) as confirmed_lytd_lbs, sum( lytd_dollars ) as confirmed_lytd_dollars, count( distinct case when datediff( month, @fiscalYearStart, purchasingMonth ) >= 0 then purchasingMonth else null end ) as ty_purchasing_month_count, 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_cases, 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_lbs, 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_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, skuId, -- operatorName, sku, skuDesc, operatorLastPurchasingMonth, tyOperatorLastPurchasingMonth ) , data as ( select cho_id, skuId, -- operatorName, 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, --purchasing_total, confirmed_total_cases, confirmed_total_lbs, confirmed_total_dollars, ytd_raw_avg_cases as ty_confirmed_avg_cases, ytd_raw_avg_lbs as ty_confirmed_avg_lbs, ytd_raw_avg_dollars as ty_confirmed_avg_dollars, 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_lytd, confirmed_lytd_cases, confirmed_lytd_lbs, confirmed_lytd_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 ) select * into [#_purchasingDataTable2#] from data; alter table [#_purchasingDataTable2#] add constraint [pk_#_purchasingDataTable2#] primary key ( cho_id, skuId );

#encodeforHTML( _r.sql )#

#_purchasingDataTable#

drop table if exists #_purchasingDataTable#; declare @metaMostRecentPurchasingMonth datetime = ; with d as ( select ownerId as mfr_idmfr_id, max( purchasingMonth ) purchasingMonth, max( purchasingMonth ) clientLastPurchaseMonth, min( purchasingMonth ) clientFirstPurchaseMonth from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where purchasingMonth <= @metaMostRecentPurchasingMonth group by ownerIdmfr_id ), w as ( select oa.ownerId as mfr_idoa.mfr_id, oa.rollup_cho_id, max( oa1.purchasingMonth ) lastAgreementOwnerPurchasingMonth from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join tbl_DW_OperatorAnalyzer#_tableSuffix# oa1 with (nolock) on oa1.agreementOwnerChoId= oa.agreementOwnerChoId on oa1.rollup_cho_id = oa.rollup_cho_id and oa1.mfr_id = oa.mfr_id group by oa.ownerIdoa.mfr_id, oa.rollup_cho_id ), o as ( select operatorChoId, clientOperatorId from tbl_DW_1fsUniverse#_tableSuffix# with ( nolock ) where clientOperatorId > 0 ), y as ( select oa.cho_id, oa.cho_type, operatorName, oa.purchasingMonth, oa.skuId, oa.sku, oa.skuDesc, oa.prodId, oa.product, oa.shortDesc, oa.categoryId, oa.catnum, oa.category, oa.prodline_id, oa.productLineCode, oa.productLine, oa.budgetCategoryId, oa.budgetCategory, oa.forecast_qty, d.clientLastPurchaseMonth as clientLastPurchaseMonth, d.clientFirstPurchaseMonth as clientFirstPurchaseMonth, ( select min( purchasingMonth ) from tbl_DW_OperatorAnalyzer#_tableSuffix# oa1 with (nolock) where oa1.cho_id = oa.cho_id and oa1.skuId = oa.skuId ) as earliestPurchaseMonth, ( select max( purchasingMonth ) from tbl_DW_OperatorAnalyzer#_tableSuffix# oa1 with (nolock) where oa1.cho_id = oa.cho_id and oa1.skuId = oa.skuId ) as latestPurchaseMonth, ( select count( distinct purchasingMonth ) from tbl_DW_OperatorAnalyzer#_tableSuffix# oa1 with (nolock) where oa1.cho_id = oa.cho_id and oa1.skuId = oa.skuId ) as totalMonthsPurchased, w.lastAgreementOwnerPurchasingMonth, ph.mfr_id, ph.mfr_name, ph.mfrSkuId, o.clientOperatorId as operatorId, case when oa.skuId is not null then oa.sku + ' - ' + oa.skuDesc else oa.sku + ' - ' + oa.skuDesc end as skuTitle, case when oa.prodId is not null then oa.product + ' - ' + oa.shortDesc else oa.product + ' - ' + oa.shortDesc end as productTitle from tbl_DW_OperatorAnalyzer#_tableSuffix# oa with (nolock) inner join tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) on ph.ownerId = oa.ownerId and ph.ownerType = oa.ownerType and ph.skuId = oa.skuId inner join w on w.rollup_cho_id = oa.rollup_cho_id and w.mfr_id = ph.mfr_id inner join o on o.operatorChoId = oa.cho_id inner join d with (nolock) on d.mfr_id = ph.mfr_id where oa.purchasingMonth <= d.purchasingMonth and oa.purchasingMonth > dateadd( month, -24, d.clientLastPurchaseMonth ) ), x as ( select cho_id, cho_type, operatorName, purchasingMonth, skuId, sku, skuDesc, prodId, product, shortDesc, categoryId, catnum, category, prodline_id, productLineCode, productLine, budgetCategoryId, budgetCategory, mfr_id, mfr_name, mfrSkuId, operatorId, skuTitle, productTitle, sum( forecast_qty ) as forecast_qty, max( clientLastPurchaseMonth ) as clientLastPurchaseMonth, min( clientFirstPurchaseMonth) as clientFirstPurchaseMonth, min ( earliestPurchaseMonth ) as earliestPurchaseMonth, max( latestPurchaseMonth ) as latestPurchaseMonth, max( totalMonthsPurchased ) as totalMonthsPurchased, max( lastAgreementOwnerPurchasingMonth ) as lastAgreementOwnerPurchasingMonth from y group by cho_id, cho_type, operatorName, purchasingMonth, skuId, sku, skuDesc, prodId, product, shortDesc, categoryId, catnum, category, prodline_id, productLineCode, productLine, budgetCategoryId, budgetCategory , mfr_id, mfr_name, mfrSkuId, operatorId, skuTitle, productTitle ), xxA as ( select x.*, max( purchasingMonth ) over( partition by mfr_id, x.cho_id ) as _operatorLastPurchaseMonth, max( lastAgreementOwnerPurchasingMonth ) over ( partition by mfr_id, x.cho_id ) as _aoLastPurchasingMonth, hq.lastPurchasingMonth as _hqLastPurchasingMonth, @metaMostRecentPurchasingMonth as _metaMostRecentPurchasingMonth from x with (nolock) left outer join #_hqLastPurchasingMonthDataTable# as hq with (nolock) on hq.cho_id = x.cho_id ), xx as ( select xxA.*, coalesce( _aoLastPurchasingMonth, _hqLastPurchasingMonth ) as _lastAgreementOwnerPurchasingMonth from xxA with (nolock) ), xxx as ( select xx.*, min( purchasingMonth ) over ( partition by mfr_id, cho_id, skuId ) as _firstKnownPurchaseDate, max( purchasingMonth ) over ( partition by mfr_id, cho_id, skuId ) as _lastPurchased, cast( sum( forecast_qty ) over ( partition by mfr_id, cho_id, skuId ) as float ) as _totalQtyRaw, count(*) over ( partition by mfr_id, cho_id, skuId ) as _purchasingMonths, last_value( forecast_qty ) over ( partition by mfr_id, cho_id, skuId order by mfr_id, cho_id, skuId ) as _lastPurchaseQty from xx with (nolock) ), xxxx as ( select xxx.*, case when _totalQtyRaw < 0 then cast( 0 as float ) else _totalQtyRaw end as _totalQty, -- purchasing frequency days case -- only once per year when totalMonthsPurchased = 1 then 0 -- purchased twice, the cycle is just repeated when totalMonthsPurchased = 2 then datediff( day, earliestPurchaseMonth, latestPurchaseMonth ) + 1 -- more than twice, so we calculate the cycledays evenly for th enumber of purchases over the time period else datediff( day, earliestPurchaseMonth, dateadd( day, -1, dateadd( month, 1, latestPurchaseMonth ))) + 1 end as _purchasingFrequencyDays, case -- only once per year when totalMonthsPurchased = 1 then 365 -- purchased twice, the cycle is just repeated when totalMonthsPurchased = 2 then datediff( day, earliestPurchaseMonth, latestPurchaseMonth ) + 1 -- more than twice, so we calculate the cycledays evenly for th enumber of purchases over the time period else cast( ( datediff( day, earliestPurchaseMonth, dateadd( day, -1, dateadd( month, 1, latestPurchaseMonth ))) + 1 ) / totalMonthsPurchased as int ) end as _purchasingCycleDays from xxx with (nolock) ), xxxxx as ( select xxxx.*, case when cast( _purchasingCycleDays / 30.0 as int ) > 0 then cast( _purchasingCycleDays / 30.0 as int ) else 1 end as _cycleMonths, case when cast( _purchasingCycleDays / 30.0 as int ) > 0 then cast( _purchasingCycleDays % 30.0 as int ) else 0 end as _cycleDays, case when _purchasingMonths < 12 then 'Y' else 'M' end as _confirmedTime, case when _purchasingMonths < 12 then cast( round( _totalQty, 0 ) as float ) else cast( round( _totalQty / _purchasingMonths, 2 ) as float ) end as _confirmedQty from xxxx with (nolock) ), xxxxxx as ( select xxxxx.*, dateadd( month, _cycleMonths, _lastPurchased ) as _checkDate from xxxxx with (nolock) ), xxxxxxx as ( select '#qmd_batch.ownerType#' as ownerType, #qmd_batch.ownerId# as ownerId, xxxxxx.*, case when _checkDate < _lastAgreementOwnerPurchasingMonth then 'N' else 'B' end as _purchasingStatus, case when _checkDate < _lastAgreementOwnerPurchasingMonth then 'Stopped Claiming!' else null end as _noSaleReason, '#_uom#' as _uom from xxxxxx with (nolock) ) select * into #_purchasingDataTable# from xxxxxxx with (nolock) order by mfr_id, cho_id, skuId, purchasingMonth; alter table #_purchasingDataTable# alter column mfr_id int not null; alter table #_purchasingDataTable# alter column cho_id int not null; alter table #_purchasingDataTable# alter column skuId int not null; alter table #_purchasingDataTable# alter column purchasingMonth datetime not null; alter table #_purchasingDataTable# add constraint PK_#_purchasingDataTable# primary key ( mfr_id, cho_id, skuId, purchasingMonth );

#encodeforHTML( _r.sql )#

#_operatorPurchasingStagingTable#

drop table if exists #_operatorPurchasingStagingTable#; select distinct ownerType, ownerId, pd1.cho_id, operatorName, pd1.skuId,sku,skuDesc, prodId,product,shortDesc,categoryId,catnum,category,prodline_id, productLineCode,productLine, budgetCategoryId,budgetCategory, _firstKnownPurchaseDate,_lastPurchased, _purchasingStatus, _confirmedQty,_confirmedTime, _uom, _noSaleReason, _checkDate, _purchasingMonths, _purchasingCycleDays, _cycleMonths, _cycleDays, _totalQty, _operatorLastPurchaseMonth, clientLastPurchaseMonth, clientFirstPurchaseMonth, earliestPurchaseMonth, latestPurchaseMonth, totalMonthsPurchased, _lastAgreementOwnerPurchasingMonth, -- _debugText, mfr_id, mfr_name, mfrSkuId, operatorId, skuTitle, productTitle, _lastPurchaseQty, pd2.first_opr_sku_purchase_month, pd2.last_opr_purchase_month, pd2.purchasing_months, pd2.confirmed_total_cases, pd2.confirmed_total_lbs, pd2.confirmed_total_dollars, pd2.confirmed_avg_cases, pd2.confirmed_avg_lbs, pd2.confirmed_avg_dollars, pd2.annual_pace_cases, pd2.annual_pace_lbs, pd2.annual_pace_dollars, pd2.ty_remaining_pace_cases, pd2.ty_remaining_pace_lbs, pd2.ty_remaining_pace_dollars, pd2.confirmed_ytd_cases, pd2.confirmed_ytd_lbs, pd2.confirmed_ytd_dollars, pd2.confirmed_lytd_cases, pd2.confirmed_lytd_lbs, pd2.confirmed_lytd_dollars, pd2.confirmed_rolling_12_cases, pd2.confirmed_rolling_12_lbs, pd2.confirmed_rolling_12_dollars, pd2.last_confirmed_cases, pd2.last_confirmed_lbs, pd2.last_confirmed_dollars, pd2.last_opr_sku_purchase_month, coalesce( pd2.confirmed_ytd_cases, 0.0 ) + coalesce( pd2.ty_remaining_pace_cases, 0.0 ) as ty_purchasing_pace_cases, coalesce( pd2.confirmed_ytd_lbs, 0.0 ) + coalesce( pd2.ty_remaining_pace_lbs, 0.0 ) as ty_purchasing_pace_lbs, coalesce( pd2.confirmed_ytd_dollars, 0.0 ) + coalesce( pd2.ty_remaining_pace_dollars, 0.0 ) as ty_purchasing_pace_dollars, pd2.ty_confirmed_avg_cases, pd2.ty_confirmed_avg_lbs, pd2.ty_confirmed_avg_dollars into [#_operatorPurchasingStagingTable#] from [#_purchasingDataTable#] pd1 inner join [#_purchasingDataTable2#] pd2 on pd2.cho_id = pd1.cho_id and pd2.skuId = pd1.skuId where _totalQty > 0;

#encodeforHTML( _r.sql )#

truncate table tbl_DW_OperatorPurchasing#_tableSuffix# insert into tbl_DW_OperatorPurchasing#_tableSuffix# ( ownerType, ownerId, cho_id, operatorName, skuId, sku, skuDesc, prodId, product, shortDesc, categoryId, catNum, category, prodline_id, productLineCode, productLine, budgetCategoryId, budgetCategory, firstPurchaseMonth, lastPurchaseMonth, purchasingStatus, quantityAmt, quantityTime, quantityUOM, reason, nextPurchaseMonth, purchasingMonths, purchasingCycleDays, cycleMonths, cycleDays, totalQty, operatorLastPurchaseMonth, clientLastPurchaseMonth, clientFirstPurchaseMonth, earliestPurchaseMonth, latestPurchaseMonth, totalMonthsPurchased, lastAgreementOwnerPurchaseMonth, -- debugText, mfrId, mfr_name, mfrSkuId, operatorId, skuTitle, productTitle, lastPurchaseAmt, -- pd2 columns first_opr_sku_purchase_month, last_opr_purchase_month, purchasing_months, confirmed_total_cases, confirmed_total_lbs, confirmed_total_dollars, confirmed_avg_cases, confirmed_avg_lbs, confirmed_avg_dollars, annual_pace_cases, annual_pace_lbs, annual_pace_dollars, ty_remaining_pace_cases, ty_remaining_pace_lbs, ty_remaining_pace_dollars, confirmed_ytd_cases, confirmed_ytd_lbs, confirmed_ytd_dollars, confirmed_lytd_cases, confirmed_lytd_lbs, confirmed_lytd_dollars, confirmed_rolling_12_cases, confirmed_rolling_12_lbs, confirmed_rolling_12_dollars, last_confirmed_cases, last_confirmed_lbs, last_confirmed_dollars, last_opr_sku_purchase_month, ty_purchasing_pace_cases, ty_purchasing_pace_lbs, ty_purchasing_pace_dollars, ty_confirmed_avg_cases, ty_confirmed_avg_lbs, ty_confirmed_avg_dollars) select ownerType, ownerId, cho_id, operatorName, skuId,sku,skuDesc, prodId,product,shortDesc, categoryId,catnum,category, prodline_id, productLineCode,productLine, budgetCategoryId,budgetCategory, _firstKnownPurchaseDate,_lastPurchased, _purchasingStatus, _confirmedQty,_confirmedTime,_uom, _noSaleReason, _checkDate, _purchasingMonths, _purchasingCycleDays, _cycleMonths, _cycleDays, _totalQty, _operatorLastPurchaseMonth, clientLastPurchaseMonth, clientFirstPurchaseMonth, earliestPurchaseMonth, latestPurchaseMonth, totalMonthsPurchased, _lastAgreementOwnerPurchasingMonth, -- _debugText, mfr_id, mfr_name, mfrSkuId, operatorId, skuTitle, productTitle, _lastPurchaseQty, -- pd2 columns first_opr_sku_purchase_month, last_opr_purchase_month, purchasing_months, confirmed_total_cases, confirmed_total_lbs, confirmed_total_dollars, confirmed_avg_cases, confirmed_avg_lbs, confirmed_avg_dollars, annual_pace_cases, annual_pace_lbs, annual_pace_dollars, ty_remaining_pace_cases, ty_remaining_pace_lbs, ty_remaining_pace_dollars, confirmed_ytd_cases, confirmed_ytd_lbs, confirmed_ytd_dollars, confirmed_lytd_cases, confirmed_lytd_lbs, confirmed_lytd_dollars, confirmed_rolling_12_cases, confirmed_rolling_12_lbs, confirmed_rolling_12_dollars, last_confirmed_cases, last_confirmed_lbs, last_confirmed_dollars, last_opr_sku_purchase_month, ty_purchasing_pace_cases, ty_purchasing_pace_lbs, ty_purchasing_pace_dollars, ty_confirmed_avg_cases, ty_confirmed_avg_lbs, ty_confirmed_avg_dollars from #_operatorPurchasingStagingTable# with (nolock)

#encodeforHTML( _r.sql )#

drop table if exists #_operatorPurchasingStagingTable#; drop table if exists #_purchasingDataTable#; drop table if exists #_hqLastPurchasingMonthDataTable#; drop table if exists #_purchasingDataTable2#; select distinct mfrId from tbl_DW_OperatorPurchasing#_tableSuffix# where mfrSkuId > 0 update op set op.mfrSku = ph.sku, op.mfrSkuDesc = ph.skuDesc, op.mfrProdId = ph.prodId, op.mfrProduct = ph.product, op.mfrShortDesc = ph.shortDesc from tbl_DW_OperatorPurchasing#_tableSuffix# op inner join tbl_DW_ProductHierarchy#_mfrTableSuffix# ph with (nolock) on ph.ownerId = and ph.ownerType = 'MFR' and ph.skuId= op.mfrSkuId

OperatorPurchasingRate2.cfm ran #( getTickCount() - _purchasingRateStart ) / 1000.0 / 60.0# Minutes

#lsnumberformat( qmd_purchasingData.recordCount )# rows to process

#htmleditformat( _r.sql )#

truncate table tbl_DW_OperatorPurchasing#_tableSuffix# #lsdateformat( qmd_metaPurchasingData.mostRecentPurchasingMonth, "MM/DD/YYYY" )# insert into tbl_DW_OperatorPurchasing#_tableSuffix# ( ownerType, ownerId, cho_id, operatorName, skuId, sku, skuDesc, prodId, product, shortDesc, categoryId, catNum, category, prodline_id, productLineCode, productLine, budgetCategoryId, budgetCategory, firstPurchaseMonth, lastPurchaseMonth, purchasingStatus, quantityAmt, quantityTime, quantityUOM, reason, nextPurchaseMonth, purchasingMonths, purchasingCycleDays, cycleMonths, cycleDays, totalQty, operatorLastPurchaseMonth, clientLastPurchaseMonth, clientFirstPurchaseMonth, earliestPurchaseMonth, latestPurchaseMonth, totalMonthsPurchased, lastAgreementOwnerPurchaseMonth, debugText, mfrId, mfr_name, mfrSkuId, operatorId, skuTitle, productTitle, lastPurchaseAmt ) values ( , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ) select distinct mfrId from tbl_DW_OperatorPurchasing#_tableSuffix# where mfrSkuId > 0 update op set op.mfrSku = ph.sku, op.mfrSkuDesc = ph.skuDesc, op.mfrProdId = ph.prodId, op.mfrProduct = ph.product, op.mfrShortDesc = ph.shortDesc from tbl_DW_OperatorPurchasing#_tableSuffix# op inner join tbl_DW_ProductHierarchy#_mfrTableSuffix# ph with (nolock) on ph.ownerId = and ph.ownerType = 'MFR' and ph.skuId= op.mfrSkuId

OperatorPurchasingRate.cfm ran #( getTickCount() - _purchasingRateStart ) / 1000.0 / 60.0# Minutes