select unitOfMeasurePlural from tblManufacturers with (nolock) where mfr_id = 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) 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, -12, 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 ) select * from x order by mfr_id, cho_id, skuId, purchasingMonth 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 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

#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