with purchasingMonth as ( select max( purchasingMonth ) purchasingMonth from tbl_DW_PurchasingMonths#_tableSuffix# with (nolock) where purchasingMonth <= getDate() ) select pm.purchasingMonth, c.fiscalyear, c.firstMonth, c.lastMonth from purchasingMonth pm inner join tbl_CRM_FiscalCalendar c with (nolock) on c.ownerId = and c.ownerType = where pm.purchasingMonth between c.firstMonth and c.lastMonth select distinct tpartnerId as userId from tbl_MFR_TPF_Detail#_tpfSuffix# with (nolock) where tpartnerType = 'USR' and forecast_status = 'Q' and mfr_id = and forecast_year = select distinct m.fspro_userId from tbl_SEC_Groups g with (nolock) inner join tbl_SEC_MemGrpLink mgl with (nolock) on mgl.groupId = g.groupId inner join tbl_Fspro_members m with (nolock) on m.ownerId = g.ownerId and m.fsl_tablecode = g.fsl_tablecode and m.fspro_userId = mgl.fspro_userId where g.groupLeaderId = and g.ownerId = and g.fsl_tablecode = and m.fspro_userId in ( ) and 1 = 2 with budgets as ( select m.fspro_userId, m.lastName + ', ' + m.firstName as salesRepName, dateFromParts( convert( int, forecast_year ), convert( int, forecast_month ), 1 ) purchasingMonth, sum( forecast_qty ) budgetQty from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) inner join tbl_Fspro_members m with (nolock) on m.fspro_userId = tpf.tPartnerId and m.ownerId = tpf.mfr_id and m.fsl_tablecode = 'MFR' where tpf.tpartnerType = 'USR' and tpf.tPartnerId in ( ) and 1 = 2 and tpf.mfr_id = and tpf.forecast_year = and tpf.forecast_status = 'Q' group by m.fspro_userId, m.firstName, m.lastName, tpf.forecast_year, tpf.forecast_month ), actuals as ( select businessOwnerUserId, purchasingMonth, sum( forecast_qty ) actualQty from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where businessOwnerUserId in ( select fspro_userId from budgets ) -- and ( cho_type = 'O' or cho_type = 'I' ) and ( cho_id = agreementOwnerChoId ) and purchasingMonth between and group by businessOwnerUserId, purchasingMonth ), lastYearRemaining as ( select businessOwnerUserId, sum( forecast_qty ) lyRemainingBalQty from tbl_DW_OperatorAnalyzer#_tableSuffix# with (nolock) where businessOwnerUserId in ( select fspro_userId from budgets ) -- and ( cho_type = 'O' or cho_type = 'I' ) and ( cho_id = agreementOwnerChoId ) and purchasingMonth between and group by businessOwnerUserId ), allBudgetsAndActuals as ( select b.fspro_userId, b.salesRepName, b.purchasingMonth, b.budgetQty, a.actualQty from budgets b left outer join actuals a on a.purchasingMonth = b.purchasingMonth and a.businessOwnerUserId = b.fspro_userId ), salesRepBudgetPerformance as ( select salesRepName, fspro_userId, sum( budgetQty ) budgetQty, sum( actualQty ) actualQty, max( purchasingMonth ) as endPurchasingMonth, min( purchasingMonth ) as startPurchasingMonth from allBudgetsAndActuals where purchasingMonth < group by salesRepName, fspro_userId ), annualBudget as ( select salesRepName, fspro_userId, sum( budgetQty ) annualBudgetQty, max( purchasingMonth ) lastPurchasingMonth, min( purchasingMonth ) as firstPurchasingMonth from allBudgetsAndActuals group by salesRepName, fspro_userId ), pipeline as ( select accountOwnerUserId, sum( pounds * impactFactor_FiscalYTLI * case when typeCode = 'PRO' then case when stageStatusCode = 'R' then -1.0 else 0.0 end when stageStatusCode = 'R' then 0.0 else 1.0 end * case when stageStatusCode = 'A' and estOrderDate < getDate() then 0.0 else 1.0 end ) pipelineQty from bi..tbl_CRM_OpportunitiesSummary#_tableSuffix# where accountOwnerUserId in ( select fspro_userId from budgets ) and year( estOrderDate ) = and ownerId = #attributes.ownerId# and ownerType = '#attributes.ownerType#' group by accountOwnerUserId ), projection as ( select bp.salesRepName, bp.fspro_userId, bp.budgetQty, bp.actualQty, bp.actualQty - bp.budgetQty as variance, bp.actualQty / bp.budgetQty as achievement, bp.startPurchasingMonth, bp.endPurchasingMonth, format( bp.startPurchasingMonth, 'MMM yyyy' ) + ' - ' + format( bp.endPurchasingMonth, 'MMM yyyy' ) dateRangeLabel, ab.annualBudgetQty, (( bp.actualQty / ( datediff( month, bp.startPurchasingMonth, bp.endPurchasingMonth ) + 1 ) ) * ( 12 - ( datediff( month, bp.startPurchasingMonth, bp.endPurchasingMonth ) + 1 )) ) + bp.actualQty as forecastAnnualActualQty, coalesce( bp.actualQty, 0.0 ) + coalesce( p.pipelineQty, 0.0 ) + coalesce( ly.lyRemainingBalQty, 0.0 ) as projectedAnnualActualQty, coalesce( p.pipelineQty, 0.0 ) as pipelineQty, coalesce( ly.lyRemainingBalQty, 0.0 ) as lyRemainingBalQty from salesRepBudgetPerformance bp left outer join lastYearRemaining ly on ly.businessOwnerUserId = bp.fspro_userId left outer join pipeline p on p.accountOwnerUserId = bp.fspro_userId left outer join annualBudget ab on ab.fspro_userId = bp.fspro_userId ) select *, projectedAnnualActualQty - annualBudgetQty as projectedAnnualVariance, projectedAnnualActualQty / annualBudgetQty as projectedAnnualAchievement from projection order by salesRepName for json path, include_null_values select opportunityId, accountOwnerShortFullName, customerName, estOrderDate, stageName, likelihoodPercent, quantityAmt as quantity, quantityUnit as UOM, typeCode, salesRepShortFullName, case when skuId is not null then skuCode + ' - ' + skuDesc else null end as skuName, prodName, quantityAmt * impactFactor_FiscalYTLI * case when typeCode = 'PRO' then case when stageStatusCode = 'R' then -1.0 else 0.0 end when stageStatusCode = 'R' then 0.0 else 1.0 end * case when stageStatusCode = 'A' and estOrderDate < getDate() then 0.0 else 1.0 end as pipelineQty from bi..tbl_CRM_OpportunitiesSummary#_tableSuffix# with (nolock) where accountOwnerUserId in ( ) and year( estOrderDate ) = order by estOrderDate for json path, include_null_values