truncate table tbl_DW_BudgetPerformance#_tablesuffix# declare @ownerId int; declare @ownerType varchar(3); -- uom = '#_uom#' set @ownerId = ; set @ownerType = ; with allTerritories as ( select territoryId from tbl_TER_Territories ter where ter.ownerId = @ownerId and ter.fsl_tablecode = @ownerType ), opportunityPeriods as ( select o.opportunityId, o.estOrderDate, o.estOrderEndDate, o.lbs, o.cases, o.dollars, p.startDate, p.endDate, p.periodName, p.fiscalStartMonth, p.fiscalEndMonth, datediff( day, o.estOrderDate, o.estOrderEndDate ) + 1 as opportunityDays, o.budgetCategoryId, o.territoryId from tbl_DW_Opportunities#_tableSuffix# o with (nolock) inner join tbl_CRM_FiscalPeriods p with (nolock) on p.ownerId = o.ownerId and p.ownerType = o.ownerType and p.periodType = 'M' and not ( p.startDate > o.estOrderEndDate or p.endDate < o.estOrderDate ) where o.statusCode = 'A' and o.pastDue = 'N' and o.ownerId = @ownerId and o.ownerType = @ownerType -- and o.opportunityId in ( 5751931, -5853510, -5853509 ) ), opportunityProrate as ( select *,-- o.opportunityId, o.estOrderDate, o.estOrderEndDate, o.lbs, p.startDate, p.endDate, p.periodName, p.fiscalStartMonth, p.fiscalEndMonth, case when estOrderDate < startDate and estOrderEndDate > endDate then datediff( day, startDate, endDate ) + 1.0 -- full month when estOrderDate >= startDate and estOrderEndDate <= endDate then ( datediff( day, estOrderDate, estOrderEndDate ) + 1.0 ) -- very short term all in one month when estOrderDate between startDate and endDate then ( datediff( day, estOrderDate, endDate ) + 1.0 ) -- start month when estOrderEndDate between startDate and endDate then ( datediff( day, startDate, estOrderEndDate ) + 1.0 ) -- end month else 0.0 end / opportunityDays as prorate from opportunityPeriods ), opportunityAllocation as ( select *, ( lbs * prorate ) as lbsAlloc, ( cases * prorate ) casesAlloc, ( dollars * prorate ) as dollarsAlloc from opportunityProrate ), pipeline as ( select a.fiscalStartMonth as salesPeriod, a.budgetCategoryId, a.territoryId, sum( casesAlloc ) as pipelineCases, sum( lbsAlloc ) as pipelineLbs, sum( dollarsAlloc ) as pipelineDollars from opportunityAllocation a group by a.fiscalStartMonth, a.budgetCategoryId, a.territoryId ), budgets as ( select tPartnerId as territoryId, bgtCategoryId as budgetCategoryId, dateFromParts( cast( forecast_year as int ), cast( forecast_month as int ), 1 ) salesPeriod , sum( forecast_qty )0.0 budgetLbs, sum( forecast_qty )0.0 budgetCases, sum( forecast_amt ) budgetDollars from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where forecast_status = 'Q' and tpartnerType = 'TER' and productLevel = 'UOM' and mfr_id = @ownerId and tPartnerId in ( select territoryId from allTerritories ) group by tPartnerId, forecast_year, forecast_month, bgtCategoryId ), actuals as ( select salesPeriod, territoryId, budgetCategoryId, sum( cases ) cases, sum( lbs ) lbs, sum( dollars ) dollars from tbl_DW_DistributorSales#_tableSuffix# s with (nolock) where salesPeriodType = 'MONTH' and ownerId = @ownerId and ownerType = @ownerType and customerType != 'REDISTRIBUTOR' group by salesPeriod, territoryId, budgetCategoryId ), forecast as ( select territoryId, dateFromParts( cast( forecast_year as int ), cast( forecast_month as int ), 1 ) as salesPeriod, bgtCategoryId as budgetCategoryId, sum( forecast_qty )0.0 forecastLbs, sum( forecast_qty )0.0 forecastCases, sum( forecast_amt ) forecastDollars from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where forecast_status = 'F' and tPartnerType = 'CDR' and mfr_id = @ownerId and territoryId in ( select territoryId from allTerritories ) group by territoryId, bgtCategoryId, forecast_year, forecast_month select territoryId, dateFromParts( cast( forecast_year as int ) + 1, cast( forecast_month as int ), 1 ) as salesPeriod, bgtCategoryId as budgetCategoryId, sum( forecast_qty )0.0 forecastLbs, sum( forecast_qty )0.0 forecastCases, sum( forecast_amt ) forecastDollars from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where forecast_status = 'A' and tPartnerType = 'CDR' and mfr_id = @ownerId and territoryId in ( select territoryId from allTerritories ) group by territoryId, bgtCategoryId, forecast_year, forecast_month ), budgetsActuals as ( select coalesce( a.territoryId, b.territoryId ) territoryId, coalesce( a.budgetCategoryId, b.budgetCategoryId ) budgetCategoryId, coalesce( a.salesPeriod, b.salesPeriod ) salesPeriod, b.budgetCases, b.budgetLbs, b.budgetDollars, a.cases, a.lbs, a.dollars from budgets b full outer join actuals a on a.salesPeriod = b.salesPeriod and a.territoryId = b.territoryId and a.budgetCategoryId = b.budgetCategoryId ), budgetsActualsPipeline as ( select coalesce( p.territoryId, ba.territoryId ) territoryId, coalesce( p.budgetCategoryId, ba.budgetCategoryId ) budgetCategoryId, coalesce( p.salesPeriod, ba.salesPeriod ) salesPeriod, ba.budgetCases, ba.budgetLbs, ba.budgetDollars, ba.cases, ba.lbs, ba.dollars, p.pipelineCases, p.pipelineLbs, p.pipelineDollars from budgetsActuals ba full outer join pipeline p on p.salesPeriod = ba.salesPeriod and p.territoryId = ba.territoryId and p.budgetCategoryId = ba.budgetCategoryId ), budgetActualsPipelineForecast as ( select coalesce( f.territoryId, bap.territoryId ) territoryId, coalesce( f.budgetCategoryId, bap.budgetCategoryId ) budgetCategoryId, coalesce( f.salesPeriod, bap.salesPeriod ) salesPeriod, bap.budgetCases, bap.budgetLbs, bap.budgetDollars, bap.cases, bap.lbs, bap.dollars, bap.pipelineCases, bap.pipelineLbs, bap.pipelineDollars, f.forecastCases, f.forecastLbs, f.forecastDollars from budgetsActualsPipeline bap full outer join forecast f on f.salesPeriod = bap.salesPeriod and f.territoryId = bap.territoryId and f.budgetCategoryId = bap.budgetCategoryId ), territories as ( select territoryId, territoryPath, name as territoryName, regionName, divisionName from tbl_TER_TErritories with (nolock) where ownerId = @ownerId and fsl_tablecode = @ownerType ), budgetCategories as ( select distinct budgetCategoryId, budgetCategory from tbl_DW_ProductHierarchy#_tableSuffix# with (nolock) where ownerId = @ownerId and ownerType = @ownerType and budgetCategoryId > 0 ) , territoryBudgetCategoryManagers as ( select ter.territoryId, ter.name as territoryName, nullif( ter.parentTerritoryId, 0 ) parentTerritoryId, ter.fsl_tablecode as ownerType, ter.ownerId, bc.budgetCategoryId, bc.budgetCategory, bcm.budgetCategoryManagerId, bcm.fspro_userId from budgetCategories bc inner join tbl_TER_Territories ter with (nolock) on ter.ownerId = @ownerId and ter.fsl_tablecode = @ownerType left outer join tbl_TER_BudgetCategoryManagers bcm with (nolock) on bcm.ownerId = ter.ownerId and bcm.ownerType = ter.fsl_tablecode and bcm.budgetCategoryId = bc.budgetCategoryId and bcm.territoryId = ter.territoryId ), budgetCategoryManagersRaw as ( select ownerId, ownerType, territoryId, territoryName, budgetCategoryId, budgetCategory, fspro_userId from territoryBudgetCategoryManagers where parentTerritoryId is null union all select t.ownerId, t.ownerType, t.territoryId, t.territoryName, t.budgetCategoryId, t.budgetCategory, coalesce( t.fspro_userId, bcm.fspro_userId ) fspro_userId from budgetCategoryManagersRaw bcm inner join territoryBudgetCategoryManagers t on t.parentTerritoryId = bcm.territoryId and t.budgetCategoryId = bcm.budgetCategoryId ), budgetCategoryManagers as ( select bcm.ownerId, bcm.ownerType, bcm.territoryId, bcm.budgetCategoryId, bcm.fspro_userId as budgetCategoryMgr_fspro_userId, m.firstName as budgetCategoryMgr_firstName, m.lastName as budgetCategoryMgr_lastName, m.email as budgetCategoryMgr_email from budgetCategoryManagersRaw bcm left outer join tbl_FSPro_members m on m.fspro_userId = bcm.fspro_userId ) insert into tbl_DW_BudgetPerformance#_tableSuffix# ( ownerType, ownerId, salesPeriod, territoryId, territoryPath, territoryName, regionName, divisionName, budgetCategoryId, budgetCategory, budgetCases, actualCases, pipelineCases, forecastCases, budgetLbs, actualLbs, pipelineLbs, forecastLbs, budgetDollars, actualDollars, pipelineDollars, forecastDollars, budgetCategoryMgr_fspro_userId, budgetCategoryMgr_firstName, budgetCategoryMgr_lastName, budgetCategoryMgr_email ) select @ownerType ownerType, @ownerId as ownerId, bapf.salesPeriod, bapf.territoryId, t.territoryPath, t.territoryName, t.regionName, t.divisionName, bapf.budgetCategoryId, g.budgetCategory, bapf.budgetCases, bapf.cases as actualCases, bapf.pipelineCases, bapf.forecastCases, bapf.budgetLbs, bapf.lbs as actualLbs, bapf.pipelineLbs, bapf.forecastLbs, bapf.budgetDollars, bapf.dollars as actualDollars, bapf.pipelineDollars, bapf.forecastDollars, bcm.budgetCategoryMgr_fspro_userId, bcm.budgetCategoryMgr_firstName, bcm.budgetCategoryMgr_lastName, bcm.budgetCategoryMgr_email from budgetActualsPipelineForecast bapf left outer join territories t on t.territoryId = bapf.territoryId left outer join budgetCategories g on g.budgetCategoryId = bapf.budgetCategoryId left outer join budgetCategoryManagers bcm on bcm.territoryId = bapf.territoryId and bcm.budgetCategoryId = bapf.budgetCategoryId

#htmleditformat( _r.sql )#