truncate table tbl_DW_DistributorAnalyzer#_tableSuffix#
with
sales as (
select tpf.mfr_id as ownerId, as ownerType, convert( int, tpf.forecast_year ) purchasingYear, convert( int, tpf.forecast_month ) purchasingMonth, tpf.forecast_qty, tpf.forecast_amt,
s.skuId, s.sku, s.skuDesc, d.dstId, d.dstName, d.parentDstId, d.parentDstName, d.dstType,
d.dstAddr1, d.dstAddr2, d.dstCity, d.dstState, d.dstZip, d.cdr_recordId
from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock)
inner join tbl_PRD_Skus s with (nolock)
on s.ownerId = tpf.mfr_id
and s.fsl_tablecode =
and s.skuId = tpf.skuId
inner join tbl_DW_1fsDistributors#_tableSuffix# d with (nolock)
on d.ownerId = tpf.mfr_id
and d.ownerType =
and d.cdr_recordId = tpf.tPartnerId
where tpf.forecast_status = 'A'
and tpf.mfr_id =
and tpf.tPartnerType = 'CDR'
and convert( int, tpf.forecast_year ) between ( #year(now())# -3 ) and ( #year(now())# + 2 )
and convert( int, tpf.forecast_month ) between 1 and 12
),
territoriesByLibraryPre as (
select ownerId, fsl_tablecode ownerType, fsl_dstId, min( cdr_territoryId ) territoryId
from tbl_CDR_distributors cdr
where ownerId =
and fsl_tablecode =
group by ownerId, fsl_tablecode, fsl_dstId
),
territoriesByLibrary as (
select t1.ownerId, t1.ownerType, t1.fsl_dstId, t.territoryId, t.name as territoryName
from territoriesByLibraryPre t1
left outer join tbl_TER_Territories t with (nolock)
on t.territoryId = t1.territoryId
and t.ownerId = t1.ownerId
and t.fsl_tablecode = t1.ownerType
where t1.ownerId =
and t1.ownerType =
),
data as (
select sales.ownerId, sales.ownerType, sales.dstName, sales.dstId, sales.dstType, sales.cdr_recordId,
sales.purchasingYear, sales.purchasingMonth, sales.forecast_qty, sales.forecast_amt, sales.skuId, sales.sku, sales.skuDesc,
prd.prodId, prd.product, prd.shortDesc, prd.categoryId, prd.catnum, prd.category, prd.prodline_id, prd.productLineCode, prd.productLine,
ter.territoryId, ter.territoryName,
sales.dstAddr1, sales.dstAddr2, sales.dstCity, sales.dstState, sales.dstZip,
sales.parentDstId, sales.parentDstName,
prd.budgetCategoryId, prd.budgetCategory, prd.productHierarchyPath
from sales
left outer join territoriesByLibrary ter
on ter.ownerId = sales.ownerId
and ter.ownerType = sales.ownerType
and ter.fsl_dstId = sales.dstId
left outer join tbl_DW_ProductHierarchy#_tableSuffix# prd with (nolock)
on prd.ownerId = sales.ownerId
and prd.ownerType = sales.ownerType
and prd.skuId = sales.skuId
)
insert into tbl_DW_DistributorAnalyzer#_tableSuffix# (
ownerType, ownerId, dstId, dstName, dstAddr1, dstAddr2, dstCity, dstState, dstZip, dstType, cdr_recordId,
parentDstId, parentDstName,
territoryId, territoryName,
skuId, sku, skuDesc, prodId, product, shortDesc, categoryId, catnum, category, prodline_id, productLineCode, productLine, productHierarchyPath, budgetCategoryId, budgetCategory,
purchasingMonth, forecast_qty, forecast_amt
)
select ownerType, ownerId, dstId, dstName, dstAddr1, dstAddr2, dstCity, dstState, dstZip, dstType, cdr_recordId,
parentDstId, parentDstName,
territoryId, territoryName,
skuId, sku, skuDesc, prodId, product, shortDesc, categoryId, catnum, category, prodline_id, productLineCode, productLine, productHierarchyPath, budgetCategoryId, budgetCategory,
datefromParts( purchasingYear, purchasingMonth, 1 ) purchasingYear, forecast_qty, forecast_amt
from data
with unitPrices as (
select purchasingMonth, skuId, forecast_amt / forecast_qty as unitPrice
from tbl_DW_DistributorAnalyzer#_tableSuffix# with (nolock)
where forecast_qty > 0
)
, avgUnitPriceByMonth as (
select purchasingMonth, skuId, avg( unitPrice ) avgUnitPrice, row_number() over ( partition by skuId order by purchasingMonth desc ) r
from unitPrices
group by purchasingMonth, skuId
),
mostRecentPrices as (
select * from avgUnitPriceByMonth where r = 1
)
update h
set h.distributorSales = case
when exists ( select *
from tbl_DW_DistributorAnalyzer#_tableSuffix# oa
where oa.ownerId = h.ownerId
and oa.ownerType = h.ownerType
and oa.skuId = h.skuId )
then 'Y' else 'N' end,
mostRecentPrice_USD = mrp.avgUnitPrice
from tbl_DW_ProductHierarchy#_tableSuffix# h
left outer join mostRecentPrices mrp
on mrp.skuId = h.skuId
where h.ownerId =
and h.ownerType =