select m.fiscalStartMonth as prorateMonth, m.percentComplete as prorateRate, y.fiscalStartMonth startMonth, y.fiscalEndMonth endMonth, y.fiscalYear from tbl_CRM_FiscalPeriods m with (nolock) inner join tbl_CRM_FiscalPeriods y with (nolock) on y.ownerId = m.ownerId and y.ownerType = m.ownerType and y.fiscalYear = m.fiscalYear and y.periodType = 'Y' where m.ownerId = and m.ownerType = and m.periodType = 'M' and m.percentComplete > 0 and m.percentComplete < 1 select top 1 m.fiscalStartMonth as prorateMonth, m.percentComplete as prorateRate, y.fiscalStartMonth startMonth, y.fiscalEndMonth endMonth, y.fiscalYear from tbl_CRM_FiscalPeriods m with (nolock) inner join tbl_CRM_FiscalPeriods y with (nolock) on y.ownerId = m.ownerId and y.ownerType = m.ownerType and y.fiscalYear = m.fiscalYear and y.periodType = 'Y' where m.ownerId = and m.ownerType = and m.periodType = 'M' and m.percentComplete = 0 order by prorateMonth ---> declare @ownerId as int; declare @ownerType as varchar(3); declare @startMonth as date; declare @endMonth as date; declare @prorateMonth as date; declare @prorateRate as float; set @prorateMonth = ; set @prorateRate = ; set @ownerId = ; set @ownerType = ; set @startMonth = ; set @endMonth = ; with prorated as ( select territoryId, territoryPath, budgetCategoryId, budgetCategory, salesPeriod, case when salesPeriod = @prorateMonth then @prorateRate else null end as prorate , coalesce( budget#_uomSuffix#, 0.0 ) as fullBudget#_uomSuffix#, case when salesPeriod = @prorateMonth then @prorateRate when salesPeriod > @prorateMonth then 0.0 else 1.0 end * coalesce( budget#_uomSuffix#, 0.0 ) as budget#_uomSuffix#, coalesce( actual#_uomSuffix#, 0.0 ) as actual#_uomSuffix#, case when salesPeriod = @prorateMonth then 1.0 - @prorateRate when salesPeriod > @prorateMonth then 1.0 else 0.0 end * coalesce( pipeline#_uomSuffix#, 0.0 ) as pipeline#_uomSuffix#, case when salesPeriod = @prorateMonth then 1.0 - @prorateRate when salesPeriod > @prorateMonth then 1.0 else 0.0 end * coalesce( forecast#_uomSuffix#, 0.0 ) as forecast#_uomSuffix# from tbl_DW_BudgetPerformance#_tableSuffix# with (nolock) where salesPeriod between @startMonth and @endMonth and territoryId in ( ) and ( 1 = 2 or territoryPath like ) and budgetCategoryId in ( ) ), lastYearRemainder as ( select salesPeriod, budgetCategoryId, territoryId, case when salesPeriod = dateadd( year, -1, @prorateMonth ) then 1.0 - @prorateRate else 1.0 end * actualCases as actualCases, case when salesPeriod = dateadd( year, -1, @prorateMonth ) then 1.0 - @prorateRate else 1.0 end * actualLbs as actualLbs, case when salesPeriod = dateadd( year, -1, @prorateMonth ) then 1.0 - @prorateRate else 1.0 end * actualDollars as actualDollars from tbl_DW_BudgetPerformance#_tableSuffix# with (nolock) where salesPeriod >= dateadd( year, -1, @prorateMonth ) and salesPeriod <= dateadd( year, -1, @endMonth ) and territoryId in ( ) and ( 1 = 2 or territoryPath like ) and budgetCategoryId in ( ) ), lastYear as ( select salesPeriod, budgetCategoryId, territoryId, actualCases, actualLbs, actualDollars from tbl_DW_BudgetPerformance#_tableSuffix# with (nolock) where salesPeriod >= dateadd( year, -1, @startMonth ) and salesPeriod <= dateadd( year, -1, @endMonth ) and territoryId in ( ) and ( 1 = 2 or territoryPath like ) and budgetCategoryId in ( ) ), result as ( select sum( ty.budget#_uom# ) as budget, sum( ty.actual#_uom# ) as actual, sum( ty.pipeline#_uom# ) as pipeline, sum( ty.forecast#_uom# ) as forecast, sum( ty.fullBudget#_uom# ) as fullBudget, sum( lyr.actual#_uom# ) as lastYearRemaining, sum( ly.actual#_uom# ) as lastYearActual, @prorateMonth prorateMonth, @prorateRate prorateRate, @startMonth startMonth, @endMonth endMonth, '#_uom#' as uom from prorated ty left outer join lastYearRemainder lyr on lyr.territoryId = ty.territoryId and lyr.budgetCategoryId = ty.budgetCategoryId and lyr.salesPeriod = dateadd( year, -1, ty.salesPeriod ) left outer join lastYear ly on ly.territoryId = ty.territoryId and ly.budgetCategoryId = ty.budgetCategoryId and ly.salesPeriod = dateadd( year, -1, ty.salesPeriod ) ) #forJSON( 'result', attributes.dataStore, 'startMonth' )#